表一保存到表三

zy19880405

文件里已经有了两段宏代码了，希望大神修改一下。。说明都已经在附件里了

liulang0808

楼主的代码已经很清晰了。
a = ThisWorkbook.Sheets("销售统计单").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("快递查询").Cells(a + 1, "A") = Sheets("销售清单").Cells(2, "F")
Sheets("快递查询").Cells(a + 1, "b") = Sheets("销售清单").Cells(2, "c")
Sheets("快递查询").Cells(a + 1, "d") = Sheets("销售清单").Cells(4, "c")

快点单号列先修改单元格格式为文本

zy19880405

liulang0808 发表于 2018-7-12 13:27
楼主的代码已经很清晰了。
a = ThisWorkbook.Sheets("销售统计单").Cells(Rows.Count, "A").End(xlUp).Row ...

版主 您好，这个我也试过的。但是我产品那一些如果有数据放进去  保存到快递查询那一页  就会多出几行空的。（我有几个产品他只是在最后一行保存了单号 前几行都是空白行）   我快递查询页实际上只需要一行就可以了

liulang0808

zy19880405 发表于 2018-7-12 13:33
版主 您好，这个我也试过的。但是我产品那一些如果有数据放进去  保存到快递查询那一页  就会多出几行空 ...

把模拟附件再传一次，包括变通的代码及数据
还有楼主检查下表格内数据是否不可见字符占用行

zy19880405

liulang0808 发表于 2018-7-12 13:27
楼主的代码已经很清晰了。
a = ThisWorkbook.Sheets("销售统计单").Cells(Rows.Count, "A").End(xlUp).Row ...

Sub 保存汇总()
Dim a As Long
With ThisWorkbook.Sheets("销售统计单")
a = ThisWorkbook.Sheets("销售统计单").Cells(Rows.Count, "A").End(xlUp).Row
b = ThisWorkbook.Sheets("快递查询").Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To 20
   If ThisWorkbook.Sheets("销售清单").Cells(i + 5, "B") <> "" Then
        n = n + 1
        .Cells(n + a, "A") = ThisWorkbook.Sheets("销售清单").Cells(2, "F")
        .Cells(n + a, "B") = ThisWorkbook.Sheets("销售清单").Cells(2, "C")
        .Cells(n + a, "C") = ThisWorkbook.Sheets("销售清单").Cells(3, "C")
        .Cells(n + a, "D") = ThisWorkbook.Sheets("销售清单").Cells(i + 5, "B")
        .Cells(n + a, "E") = ThisWorkbook.Sheets("销售清单").Cells(i + 5, "C")
        .Cells(n + a, "F") = ThisWorkbook.Sheets("销售清单").Cells(i + 5, "D")
        .Cells(n + a, "G") = ThisWorkbook.Sheets("销售清单").Cells(i + 5, "E")
        Sheets("快递查询").Cells(b + 1, "A") = Sheets("销售清单").Cells(2, "F")
        Sheets("快递查询").Cells(b + 1, "b") = Sheets("销售清单").Cells(2, "c")
        Sheets("快递查询").Cells(b + 1, "d") = Sheets("销售清单").Cells(4, "c")

   End If
Next i
End With
ThisWorkbook.save
MsgBox "保存成功"
End Sub

版主我改成了这样就可以了   但是保存的速度感觉不快。

liulang0808

Sub 保存汇总()
Dim a As Long
Application.ScreenUpdating = False
b = ThisWorkbook.Sheets("快递查询").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("快递查询").Cells(b + 1, "A") = Sheets("销售清单").Cells(2, "F")
Sheets("快递查询").Cells(b + 1, "b") = Sheets("销售清单").Cells(2, "c")
Sheets("快递查询").Cells(b + 1, "d") = Sheets("销售清单").Cells(4, "c")
With ThisWorkbook.Sheets("销售统计单")
a = ThisWorkbook.Sheets("销售统计单").Cells(Rows.Count, "A").End(xlUp).Row

For i = 1 To 20
   If ThisWorkbook.Sheets("销售清单").Cells(i + 5, "B") <> "" Then
        n = n + 1
        .Cells(n + a, "A") = ThisWorkbook.Sheets("销售清单").Cells(2, "F")
        .Cells(n + a, "B") = ThisWorkbook.Sheets("销售清单").Cells(2, "C")
        .Cells(n + a, "C") = ThisWorkbook.Sheets("销售清单").Cells(3, "C")
        .Cells(n + a, "D") = ThisWorkbook.Sheets("销售清单").Cells(i + 5, "B")
        .Cells(n + a, "E") = ThisWorkbook.Sheets("销售清单").Cells(i + 5, "C")
        .Cells(n + a, "F") = ThisWorkbook.Sheets("销售清单").Cells(i + 5, "D")
        .Cells(n + a, "G") = ThisWorkbook.Sheets("销售清单").Cells(i + 5, "E")
        

   End If
Next i
End With
ThisWorkbook.Save
Application.ScreenUpdating = True
MsgBox "保存成功"
End Sub