试图复制和粘贴一些可变数据

Question

我正在处理一个包含大量宏的现有excel文件，并希望在不复制空白单元格的情况下将5个不同工作表中的可变数据复制和粘贴到其他5个工作表中。这就是我到目前为止所做的，并给出了运行时错误1004：

Sub Macro1()

    Sheets("Hulp_IO").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("IO").Select
    Range("B2").Select
    ActiveSheet.Paste

    Sheets("Hulp_Modbus_PMSX_Lees_Tags").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Modbus_Lees_Tags_PMSX").Select
    Range("A2").Select
    ActiveSheet.Paste

    Sheets("Hulp_Modbus_PMSX_Schrijf_Tags").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Modbus_Schrijf_Tags_PMSX").Select
    Range("A2").Select
    ActiveSheet.Paste

    Sheets("Hulp_Modbus_Pakscan_Lees_Tags").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Modbus_Lees_Tags_PackScan").Select
    Range("A2").Select
    ActiveSheet.Paste

    Sheets("Hulp_Modbus_Pakscan_Schrijf_Tag").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Modbus_Schrijf_Tags_PackScan").Select
    Range("A2").Select
    ActiveSheet.Paste
    Sheets("Start").Select

End Sub

Answer 1

下面是对您的代码的主要回顾，因为.Select命令实际上是重新源的--贪婪，现在它的可读性要高得多！

我不知道您的代码在哪一行出现了错误，但是这是一个重要的信息，因此，即使这解决了您的问题，也要添加它！)

以下是代码：

Sub Nito_Nascimento()
Dim WsFrom As Worksheet, _
    WsTo As Worksheet

    Set WsFrom = Sheets("Hulp_IO")
    Set WsTo = Sheets("IO")

    WsFrom.Range("A1", WsFrom.Range("A" & WsFrom.Rows.Count).End(xlUp)).Copy
    WsTo.Range("B2").PasteSpecial _
                        Paste:=xlPasteValues, _
                        Operation:=xlNone, _
                        SkipBlanks:=True, _
                        Transpose:=False

    Set WsFrom = Sheets("Hulp_Modbus_PMSX_Lees_Tags")
    Set WsTo = Sheets("Modbus_Lees_Tags_PMSX")
    Application.CutCopyMode = False
    WsFrom.Range("A1", WsFrom.Range("A" & WsFrom.Rows.Count).End(xlUp)).Copy
    WsTo.Range("A2").PasteSpecial _
                        Paste:=xlPasteValues, _
                        Operation:=xlNone, _
                        SkipBlanks:=True, _
                        Transpose:=False

    Set WsFrom = Sheets("Hulp_Modbus_PMSX_Schrijf_Tags")
    Set WsTo = Sheets("Modbus_Schrijf_Tags_PMSX")
    Application.CutCopyMode = False
    WsFrom.Range("A1", WsFrom.Range("A" & WsFrom.Rows.Count).End(xlUp)).Copy
    WsTo.Range("A2").PasteSpecial _
                        Paste:=xlPasteValues, _
                        Operation:=xlNone, _
                        SkipBlanks:=True, _
                        Transpose:=False

    Set WsFrom = Sheets("Hulp_Modbus_Pakscan_Lees_Tags")
    Set WsTo = Sheets("Modbus_Lees_Tags_PackScan")
    Application.CutCopyMode = False
    WsFrom.Range("A1", WsFrom.Range("A" & WsFrom.Rows.Count).End(xlUp)).Copy
    WsTo.Range("A2").PasteSpecial _
                        Paste:=xlPasteValues, _
                        Operation:=xlNone, _
                        SkipBlanks:=True, _
                        Transpose:=False

    Set WsFrom = Sheets("Hulp_Modbus_Pakscan_Schrijf_Tag")
    Set WsTo = Sheets("Modbus_Schrijf_Tags_PackScan")
    Application.CutCopyMode = False
    WsFrom.Range("A1", WsFrom.Range("A" & WsFrom.Rows.Count).End(xlUp)).Copy
    WsTo.Range("A2").PasteSpecial _
                        Paste:=xlPasteValues, _
                        Operation:=xlNone, _
                        SkipBlanks:=True, _
                        Transpose:=False

    Application.CutCopyMode = False
    Sheets("Start").Activate
    Set WsFrom = Nothing
    Set WsTo = Nothing

End Sub