'如果不明白其中部分语句的含义，可以截选部分行做成一个sub,试运行看效果；

Sub 做发票清单和装箱清单()

Sheets("PACKING LIST").Select

Range("B65536").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("B65536").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("B65536").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

'《制单》中删除汇总行；

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

End Sub