excel - VBA Excel将动态范围从两张纸合并为一张，1004粘贴错误

Question

我正在尝试将来自两个不同电子表格的数据合并为一个，它成为几个数据透视表的数据源。两张表都有不同的布局，所以我在第一张表中循环查找列，复制它下面的数据范围，然后粘贴到 wDATA 表中。然后转到下一张表，找到相同的标题，然后粘贴到第一个 block 下方。
我得到了我最喜欢的错误，1004。我尝试了不同的礼仪和方法，但它不会粘贴，所以这就是我开始的。 Link是具有较大位和数据的文件。我保证它干净。有什么帮助吗？

            For x = 1 To iEndcol 'TOP SECTION OF DATA  -FBL5N
            If InStr(Cells(1, x), "Sold") Then
                Range(Cells(2, x), Cells(lEndRowA, x)).Copy _
                    Destination:=wDATA.Range(Cells(1, 1), Cells(lEndRowA, 1))
            ElseIf Cells(1, x) = "Invoice#" Then
                Range(Cells(2, x), Cells(lEndRowA, x)).Copy _
                    Destination:=wDATA.Range(Cells(1, 2), Cells(lEndRowA, 2))
            ElseIf Cells(1, x) = "Billing Doc" Then
                Range(Cells(2, x), Cells(lEndRowA, x)).Copy _
                    Destination:=wDATA.Range(Cells(1, 3), Cells(lEndRowA, 3))
            ElseIf InStr(Cells(1, x), "Cust Deduction") Then
                Range(Cells(2, x), Cells(lEndRowA, x)).Copy _
                    Destination:=wDATA.Range(Cells(1, 4), Cells(lEndRowA, 4))
            ElseIf Cells(1, x) = "A/R Adjustment" Then
                Range(Cells(2, x), Cells(lEndRowA, x)).Copy _
                    Destination:=wDATA.Range(Cells(1, 5), Cells(lEndRowA, 5))
            ElseIf InStr(Cells(1, x), "Possible Repay") Then
                Range(Cells(2, x), Cells(lEndRowA, x)).Copy _
                    Destination:=wDATA.Range(Cells(1, 6), Cells(lEndRowA, 6))
            ElseIf InStr(Cells(1, x), "Profit") Then
                Range(Cells(2, x), Cells(lEndRowA, x)).Copy _
                    Destination:=wDATA.Range(Cells(1, 7), Cells(lEndRowA, 7))
            End If
        Next
    End If
    ' DO NOT REDEFINE lEndrowA until all data is moved
    ' Fills in data from the second source, wLID
    If Not wLID Is Nothing Then
        wLID.Activate
        lEndRowB = Cells(4650, 1).End(xlUp).Row
        iEndcol = Cells(1, 1).End(xlToRight).Column
        For x = 1 To iEndcol 'BOTTOM
            If InStr(Cells(1, x), "Sold-To") Then
                Range(Cells(2, x), Cells(lEndRowB, x)).Copy _
                    Destination:=wDATA.Range(Cells(1, 1), Cells(lEndRowA + lEndRowB, 1))
            ElseIf Cells(1, x) = "Invoice#" Then
                Range(Cells(2, x), Cells(lEndRowB, x)).Copy _
                    Destination:=wDATA.Range(Cells(1, 2), Cells(lEndRowA + lEndRowB, 2))
            ElseIf Cells(1, x) = "Billing Doc" Then
                Range(Cells(2, x), Cells(lEndRowB, x)).Copy _
                    Destination:=wDATA.Range(Cells(1, 3), Cells(lEndRowA + lEndRowB, 3))
            ElseIf InStr(Cells(1, x), "Cust Deduction") Then
                Range(Cells(2, x), Cells(lEndRowB, x)).Copy _
                    Destination:=wDATA.Range(Cells(1, 4), Cells(lEndRowA + lEndRowB, 4))
            ElseIf Cells(1, x) = "A/R Adjustment" Then
                Range(Cells(2, x), Cells(lEndRowB, x)).Copy _
                    Destination:=wDATA.Range(Cells(1, 5), Cells(lEndRowA + lEndRowB, 5))
            ElseIf InStr(Cells(1, x), "Possible Repay") Then
                Range(Cells(2, x), Cells(lEndRowB, x)).Copy _
                    Destination:=wDATA.Range(Cells(1, 6), Cells(lEndRowA + lEndRowB, 6))
            ElseIf InStr(Cells(1, x), "Profit") Then
                Range(Cells(2, x), Cells(lEndRowB, x)).Copy _
                    Destination:=wDATA.Range(Cells(1, 7), Cells(lEndRowA + lEndRowB, 7))
            End If
        Next
    End If

Answer 1

问题在于这行代码:

wDATA.Range(Cells(1, 1), Cells(lEndRowA + lEndRowB, 1))

您已获得 Range 的资格对象，但不是 Cells对象。没有资格， ActiveSheet假设。试试这个:

wDATA.Range(wDATA.Cells(1, 1), wDATA.Cells(lEndRowA + lEndRowB, 1))