carton qty是什么意思（基础数据自动计算输出多个报表数据）

利用一个工作表（计划数据）和另一个工作表（基础数据），自动生成报表数据（两个工作表，分别是发票和装箱单）。

1 工作表（计划数据）

2 工作表（基础数据）

3 自动生成报表数据（工作表，发票）

4 自动生成报表数据（工作表，装箱单）

5 数据生成过程视频演示

<script src="https://s0.pstatp.com/tt_player/tt.player.js?v=20160723"></script>

6 代码

'如果不明白其中部分语句的含义，可以截选部分行做成一个sub,试运行看效果；
Sub 做发票清单和装箱清单()
Dim tns As Long
Sheets("PACKING LIST").Select
tns = Sheets("PACKING LIST").Cells.Rows.Count
Range("B" & tns).End(xlUp).Select
ANdel = Selection.Row - 1
If ANdel > 23 Then
 Rows("23:" & ANdel).Delete
End If
Rows("17:22").Select
Selection.ClearContents
Sheets("INVOICE").Select
Range("B" & tns).End(xlUp).Select
ANdel = Selection.Row - 1
If ANdel > 25 Then
 Rows("25:" & ANdel).Delete
End If
Rows("19:24").Select
Selection.ClearContents
'《制单》工作表的数据处理
'《制单》中删除汇总数据行前的空行；
On Error GoTo errorhandler '当未有空号时会出错
Sheets("制单").Select
Range("A" & tns).End(xlUp).Select
ANdel = Selection.Row
rj = 1
Dim titleR '标题行
titleR = 3
For num = titleR + rj To ANdel
 If Range("A" & titleR + rj) = "" Then
 Range("A" & titleR + rj).EntireRow.Select
 Selection.Delete
 rj = rj - 1
 End If
 rj = rj + 1
Next num
Resume
'以下代码也可删除空行，更好理解；
'Dim firstRow As Long
'Dim LastRow As Long
'Dim i As Long
'firstRow = ActiveSheet.UsedRange.Row
'LastRow = firstRow + ActiveSheet.UsedRange.Rows.Count - 1
'For i = LastRow To firstRow Step -1
 'If Application.WorksheetFunction.CountA(Rows(i)) = 0 Then
 'Rows(i).Delete
 'End If
'Next i
'《制单》中删除汇总行；
errorhandler:
Sheets("制单").Select
Dim tt
Dim tn
Dim tt1
tt1 = 0
tn = 4
Range("G3").Select
Range(Selection, Selection.End(xlDown)).Select '选择区域：实际出货数量+后面的数量，在遇到空格前停止选择
tt = Selection.Count
For num11 = 1 To tt
 If Range("A" & tn).Value = "Total" Then
 Range("A" & tn).EntireRow.Select
 Selection.Delete
 tn = tn - 1
 tt1 = tt1 + 1
 End If
 tn = tn + 1
Next num11
'《制单》中插入汇总行；
Range("A" & tt + 3).Value = "Total"
Range("A" & tt + 3).Activate
ActiveCell.Offset(0, 6).FormulaR1C1 = "=sum(r1c:r[-1]c)"
ActiveCell.Offset(0, 13).FormulaR1C1 = "=sum(r1c:r[-1]c)"
Range("A" & tt + 3 & ":P" & tt + 3).Select
Selection.Font.Size = 16
Selection.Font.Color = -16777024
Selection.Font.Bold = True
Selection.NumberFormatLocal = "0_);[红色](0)"
'《制单》中隔行插入空行；
Dim A As Integer '发货计划的行数-1,用于控制循环的次数，也就是插入空行的数量；
Dim B As Integer '用于控制插入行的地址的行号；
Dim C 'B列地址字符串；
Dim CartonSerial '定义行号
Dim E '控制packlist制作时安计划的行数控制循环的次数；
Dim F 'B列地址字符串；
Dim row3 '需入执行的插入次数
row3 = 0
Range("G3").Select
Range(Selection, Selection.End(xlDown)).Select '选择区域：实际出货数量+后面的数量，在遇到空格前停止选择
A = Selection.Count - 2
E = A
B = 5 '第5行是发货计划第一行的下一行
Do While A > 0
 C = "B" & B
 Range(C).Select
 Selection.EntireRow.Insert
 A = A - 1
 B = B + 2 'B最后的数值等于发货计划行数*2+5,也就是合计行的前一行的行号;
 row3 = row3 + 1
Loop
'《invoice》中插入需要的格式行
Nrow = 19
Sheets("invoice").Select
For num2 = 19 To Nrow + row3 - 3 + 1 '本身有2条数据+一条空数据+汇总行
 Rows("19:20").Select
 Selection.Copy
 Selection.Insert Shift:=xlDown
Next num2
Range("A19:I" & Nrow + row3 + 15).Select
Selection.ClearContents
For num3 = 19 To 19 + row3 - 3 + 1
 Range("K" & Nrow).Formula = "=VLOOKUP(B:B,重量与单价数据!A:D,4,0)"
 Nrow = Nrow + 2
Next num3
'《invoice》的数据处理；
B = B - 2 + tt1 * 2
Sheets("制单").Select
Range("A4", "A" & B).Select '选中发货计划的SKU（间行空格)
Selection.Copy
Sheets("INVOICE").Select
Range("B19").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("制单").Select
Range("G4", "G" & B).Select '选中发货计划的数量（间行空格)
Selection.Copy
Sheets("INVOICE").Select
Range("D19").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "PCS"
Range(Selection, ActiveCell.Offset(1, 0)).Select
Selection.Copy
Range(Selection, ActiveCell.Offset(B - 4, 0)).Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Select
ActiveCell.Formula = "=vlookup(b:b,重量与单价数据!a:b,2,0)"
Range(Selection, ActiveCell.Offset(1, 0)).Select
Selection.Copy
Range(Selection, ActiveCell.Offset(B - 4, 0)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.NumberFormat = """US$""#,##0.00_);[Red](""US$""#,##0.00)"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=RC[-4]*RC[-2]"
Range(Selection, ActiveCell.Offset(1, 0)).Select
Selection.Copy
Range(Selection, ActiveCell.Offset(B - 4, 0)).Select
ActiveSheet.Paste
ActiveCell.Offset(0, 3).Select
ActiveCell.Offset(B - 1, -7).Select
ActiveCell.FormulaR1C1 = "=sum(r1c:r[-1]c)"
ActiveCell.Offset(0, 1).Value = "PCS"
ActiveCell.Offset(0, -2).Value = "TOTAL:"
Selection.Copy
ActiveCell.Offset(0, 4).Select
ActiveSheet.Paste
'《invoice》中计算箱数；
Nrow = 19
For num3 = 19 To 19 + row3 - 1 + tt1
 Range("K" & Nrow).Value = "=VLOOKUP(B:B,重量与单价数据!A:D,4,0)"
 v1 = Range("K" & Nrow).Value
 v2 = Range("D" & Nrow).Value
 'v3 = v2 / v1
 'v4 = Application.WorksheetFunction.RoundUp(v3, 0)
 'v5 = v5 + v4 '发货箱数
 v3 = v2 Mod v1
 If v3 <> 0 Then
 If v2 < v1 Then
 v5 = v5 + 1
 Else
 v5 = v5 + 2
 End If
 Else
 v5 = v5 + 1
 End If
 Nrow = Nrow + 2
Next num3
Range("I11").Value = v5
'《PACKING LIST》工作表的数据处理；
'《PACKING LIST》中插入需要数量的格式行；
Sheets("PACKING LIST").Select
Nrow = 17
For num4 = 17 To Nrow + v5 - 3 '本身有两条数据加一条空数据（8行）
 Rows("17:18").Select
 Selection.Copy
 Selection.Insert Shift:=xlDown
Next num4
Range("A17:I" & Nrow + v5 * 2).Select
Selection.ClearContents
h = 0 '当在packing list复制invoice中的名称与数量时，当有整箱+零头箱时，需多偏移2行，代码：h=h+2
LingTou = 0 '每箱余数
NperCarton = 0 '每种产品每箱装箱数
CartonSerial = 1
Wpcs = 0 '控制总量参数
qty = 0
gqty = 0 '总支数
A = 17 '控制活动单元格的起始地址
F = "B" & A '控制活动单元格的起始地址
Sheets("PACKING LIST").Select
E = E + tt1
Do While E > 0 'activeCell首先是B列(SKU)，然后是E列(箱数，值先是Qty,然后是未取整箱数，然后再是取整的箱数),
 F = "B" & A
 '从invoice工作表用公式复制SKU；
 Range(F).Select
 ActiveCell.FormulaR1C1 = "=INVOICE!r[" & (2 - h) & "]c[0]"
 '从活动单元格开始行偏移，每运行一次，如果有零头，对应SKU的位置会多偏移h行；
 '从invoice工作表用公式复制数量；
 ActiveCell.Offset(0, 3).Select
 ActiveCell.FormulaR1C1 = "=INVOICE!R[" & (2 - h) & "]C[-1]"
 '从重量与单价数据工作表引用对应的SKU每箱包装的数量，T列，被隐藏；
 ActiveCell.Offset(0, 15).Formula = "=vlookup(b:b,重量与单价数据!a:d,4,0)"
 NperCarton = ActiveCell.Offset(0, 15).Value
 qty = ActiveCell.Value
 gqty = gqty + qty
 NwholeCarton = qty \ NperCarton 'B，As Integer,整箱箱数；
 LingTou = qty Mod NperCarton
 ActiveCell.Value = NwholeCarton
 intNwholeCarton = Int(ActiveCell.Value)
 
 Select Case LingTou
 Case 0 '发货数量为整箱没有零头的货品（一条记录，两行）；
 ActiveCell.Value = NwholeCarton
 NwholeCarton = ActiveCell.Value
 ActiveCell.Offset(0, 1).Value = "CARTONS"
 C = "( " & NperCarton & " PCS/CTN, " & NwholeCarton * NperCarton & "PCS)"
 '引用纸箱重量到S列，隐藏；
 ActiveCell.Offset(0, 14).Formula = "=vlookup(b:b,重量与单价数据!a:E,5,0)"
 '引用每支单重到R列，隐藏；
 ActiveCell.Offset(0, 13).Formula = "=vlookup(b:b,重量与单价数据!a:c,3,0)/1000"
 packageWeight = ActiveCell.Offset(0, 14).Value
 Wpcs = ActiveCell.Offset(0, 13).Value
 ActiveCell.Offset(1, 0).Value = C
 ActiveCell.Offset(0, 2).Value = NwholeCarton * (Wpcs * NperCarton + packageWeight)
 ActiveCell.Offset(0, 3).Value = "KGS"
 ActiveCell.Offset(1, 2).Value = "(@" & (Wpcs * NperCarton + packageWeight) & " kgs)"
 ActiveCell.Offset(0, 4).Value = NwholeCarton * Wpcs * NperCarton
 ActiveCell.Offset(1, 4).Value = "(@" & (Wpcs * NperCarton) & "kgs)"
 ActiveCell.Offset(0, 5).Value = "KGS"
 ActiveCell.Offset(0, 6).Formula = "=VLOOKUP(B:B,重量与单价数据!A:I,9,0)"
 ActiveCell.Offset(0, 7).Value = "x"
 ActiveCell.Offset(0, 8).Formula = "=VLOOKUP(B:B,重量与单价数据!A:J,10,0)"
 ActiveCell.Offset(0, 9).Value = "x"
 ActiveCell.Offset(0, 10).Formula = "=VLOOKUP(B:B,重量与单价数据!A:k,11,0)"
 ActiveCell.Offset(0, 11).Value = "cm"
 ActiveCell.Offset(0, 12).FormulaR1C1 = "=RC[-12]*rc[-6]*rc[-4]*rc[-2]/1000000"
 ActiveCell.Offset(0, -4).Select
 C = CartonSerial & " - " & CartonSerial + NwholeCarton - 1
 CartonSerial = CartonSerial + NwholeCarton
 ActiveCell.Value = C
 A = A + 2
 Case Else
 If qty < NperCarton Then '发货数量不够一箱的货品（一条记录，两行）；
 ActiveCell.Value = 1
 intNwholeCarton = ActiveCell.Value
 ActiveCell.Offset(0, 1).Value = "CARTONS"
 C = "( " & qty & " PCS/CTN, " & qty & "PCS)"
 ActiveCell.Offset(0, 14).Formula = "=vlookup(b:b,重量与单价数据!a:E,5,0)"
 ActiveCell.Offset(0, 13).Formula = "=vlookup(b:b,重量与单价数据!a:c,3,0)/1000"
 packageWeight = ActiveCell.Offset(0, 14).Value
 Wpcs = ActiveCell.Offset(0, 13).Value
 ActiveCell.Offset(1, 0).Value = C
 ActiveCell.Offset(0, 2).Value = 1 * (Wpcs * qty + packageWeight)
 ActiveCell.Offset(0, 3).Value = "KGS"
 ActiveCell.Offset(1, 2).Value = "(@" & (Wpcs * qty + packageWeight) & " kgs)"
 ActiveCell.Offset(0, 4).Value = 1 * Wpcs * qty
 ActiveCell.Offset(1, 4).Value = "(@" & (Wpcs * qty) & "kgs)"
 ActiveCell.Offset(0, 5).Value = "KGS"
 ActiveCell.Offset(0, 6).Formula = "=VLOOKUP(B:B,重量与单价数据!A:I,9,0)"
 ActiveCell.Offset(0, 7).Value = "x"
 ActiveCell.Offset(0, 8).Formula = "=VLOOKUP(B:B,重量与单价数据!A:J,10,0)"
 ActiveCell.Offset(0, 9).Value = "x"
 ActiveCell.Offset(0, 10).Formula = "=VLOOKUP(B:B,重量与单价数据!A:k,11,0)"
 ActiveCell.Offset(0, 11).Value = "cm"
 ActiveCell.Offset(0, 12).FormulaR1C1 = "=RC[-12]*rc[-6]*rc[-4]*rc[-2]/1000000"
 ActiveCell.Offset(0, -4).Select
 C = CartonSerial & " - " & CartonSerial + 1 - 1
 ActiveCell.Value = C
 CartonSerial = CartonSerial + 1
 A = A + 2
 
 Else '发货数量超个一箱且有零头的货品（2条记录，4行）；
 '发货数量超个一箱且有零头的货品的整箱部分；
 ActiveCell.Value = intNwholeCarton
 ActiveCell.Offset(0, 1).Value = "CARTONS"
 C = "( " & NperCarton & " PCS/CTN, " & NwholeCarton * NperCarton & "PCS)"
 ActiveCell.Offset(0, 14).Formula = "=vlookup(b:b,重量与单价数据!a:E,5,0)"
 ActiveCell.Offset(0, 13).Formula = "=vlookup(b:b,重量与单价数据!a:c,3,0)/1000"
 packageWeight = ActiveCell.Offset(0, 14).Value
 Wpcs = ActiveCell.Offset(0, 13).Value
 ActiveCell.Offset(1, 0).Value = C
 ActiveCell.Offset(0, 2).Value = intNwholeCarton * (Wpcs * NperCarton + packageWeight)
 ActiveCell.Offset(0, 3).Value = "KGS"
 ActiveCell.Offset(1, 2).Value = "(@" & (Wpcs * NperCarton + packageWeight) & " kgs)"
 ActiveCell.Offset(0, 4).Value = intNwholeCarton * Wpcs * NperCarton
 ActiveCell.Offset(1, 4).Value = "(@" & (Wpcs * NperCarton) & "kgs)"
 ActiveCell.Offset(0, 5).Value = "KGS"
 ActiveCell.Offset(0, 6).Formula = "=VLOOKUP(B:B,重量与单价数据!A:I,9,0)"
 ActiveCell.Offset(0, 7).Value = "x"
 ActiveCell.Offset(0, 8).Formula = "=VLOOKUP(B:B,重量与单价数据!A:J,10,0)"
 ActiveCell.Offset(0, 9).Value = "x"
 ActiveCell.Offset(0, 10).Formula = "=VLOOKUP(B:B,重量与单价数据!A:k,11,0)"
 ActiveCell.Offset(0, 11).Value = "cm"
 ActiveCell.Offset(0, 12).FormulaR1C1 = "=RC[-12]*rc[-6]*rc[-4]*rc[-2]/1000000"
 ActiveCell.Offset(0, -4).Select
 C = CartonSerial & " - " & CartonSerial + intNwholeCarton - 1
 CartonSerial = CartonSerial + intNwholeCarton
 ActiveCell.Value = C
 A = A + 2
 
 '发货数量超个一箱且有零头的货品的零头部分；
 Range(F).Select
 ActiveCell.Offset(2, 3).Select
 ActiveCell.Value = 1
 ActiveCell.Offset(0, 1).Value = "CARTONS"
 C = "( " & LingTou & " PCS/CTN, " & LingTou & "PCS)"
 ActiveCell.Offset(0, -3).FormulaR1C1 = "=R[-2]C"
 ActiveCell.Offset(0, 13).Formula = "=vlookup(b:b,重量与单价数据!a:c,3,0)/1000"
 Wpcs = ActiveCell.Offset(0, 13).Value
 ActiveCell.Offset(1, 0).Value = C
 ActiveCell.Offset(0, 2).Value = (Wpcs * LingTou + packageWeight)
 ActiveCell.Offset(0, 3).Value = "KGS"
 ActiveCell.Offset(1, 2).Value = "(@" & (Wpcs * LingTou + packageWeight) & " kgs)"
 ActiveCell.Offset(0, 4).Value = Wpcs * LingTou
 ActiveCell.Offset(1, 4).Value = "(@" & (Wpcs * LingTou) & "kgs)"
 ActiveCell.Offset(0, 5).Value = "KGS"
 ActiveCell.Offset(0, 6).Formula = "=VLOOKUP(B:B,重量与单价数据!A:I,9,0)"
 ActiveCell.Offset(0, 7).Value = "x"
 ActiveCell.Offset(0, 8).Formula = "=VLOOKUP(B:B,重量与单价数据!A:J,10,0)"
 ActiveCell.Offset(0, 9).Value = "x"
 ActiveCell.Offset(0, 10).Formula = "=VLOOKUP(B:B,重量与单价数据!A:k,11,0)"
 ActiveCell.Offset(0, 11).Value = "cm"
 ActiveCell.Offset(0, 12).FormulaR1C1 = "=RC[-12]*rc[-6]*rc[-4]*rc[-2]/1000000"
 ActiveCell.Offset(0, -4).Select
 C = CartonSerial & " - " & CartonSerial
 CartonSerial = CartonSerial + 1
 ActiveCell.Value = C
 A = A + 2
 h = h + 2
 End If
 End Select
 E = E - 1
Loop
'《PACKING LIST》汇总行数据处理；
Range("D16").Select
C = "( " & gqty & "PCS )"
ActiveCell.Value = C
Dim tot '汇总行的行号；
tot = A + 2
Range("C" & tot).Activate
ActiveCell.Value = CartonSerial - 1
ActiveCell.Offset(0, 2).Value = gqty
ActiveCell.Offset(0, 4).FormulaR1C1 = "=sum(r1c:r[-1]c)"
ActiveCell.Offset(0, 6).FormulaR1C1 = "=sum(r1c:r[-1]c)"
'删除《制单》中的空格
Sheets("制单").Select
Rows("3:3").Select
nj = 4
Do While Range("A" & nj).Value <> "Total"
 nj = nj + 1
Loop
On Error GoTo errorhandler2 '当未有空号时会出错
rj = 1
titleR = 3
For num = titleR + rj To nj - 1
 If Range("A" & titleR + rj) = "" Then
 Range("A" & titleR + rj).EntireRow.Select
 Selection.Delete
 rj = rj - 1
 End If
 rj = rj + 1
Next num
Resume
errorhandler2:
Sheets("PACKING LIST").Select
Range("C" & tot).Select
ActiveWindow.DisplayGridlines = False
End Sub
－End－