将特定范围的数据从一个工作簿复制到另一个工作簿

I'm trying to copy data from a workbook and paste it to another. I want it to copy a specific range, up to the last cell that contains data.

我正在尝试从工作簿中复制数据并将其粘贴到另一个工作簿。我希望它复制一个特定的范围,直到包含数据的最后一个单元格。

This is what I have so far but it gives me a 'subscript out of range' error.

这是我到目前为止,但它给了我一个'下标超出范围'的错误。

Sub BackLog()

With Workbooks("Backlog Query AHSI").Worksheets("Sheet1")
   lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With

ThisWorkbook.Worksheets("Data").Range("A2:A" & lastRow).Value = Workbooks("Backlog Query AHSI").Worksheets("Sheet1").Range("A2:A" & lastRow).Value
ThisWorkbook.Worksheets("Data").Range("L2:L" & lastRow).Value = Workbooks("Backlog Query AHSI").Worksheets("Sheet1").Range("X2:X" & lastRow).Value
ThisWorkbook.Worksheets("Data").Range("M2:AH" & lastRow).Value = Workbooks("Backlog Query AHSI").Worksheets("Sheet1").Range("L2:AG" & lastRow).Value

End Sub

2 个解决方案

#1

If you use Copy you need only specify a single cell for the destination:

如果使用“复制”,则只需为目标指定一个单元格:

Workbooks("Order Query AHSI").Worksheets("Sheet1").Range("A2:A" & lastRow).Copy ThisWorkbook.Worksheets("Data").Range("A2")

#2

Which line gives the error?
ThisWorkbook.Worksheets("Data").Range("A2:A" & lastRow).Value = Workbooks("Order Query AHSI").Worksheets("Sheet1").Range("A2:A" & lastRow).Value – user3324221 14 mins ago

哪一行给出错误? ThisWorkbook.Worksheets(“Data”)。Range(“A2:A”&lastRow).Value = Workbooks(“Order Query AHSI”)。工作表(“Sheet1”)。范围(“A2:A”和lastRow).Value - 14分钟前用户3324221

What is value of lastRow at that point? it's 1245

那个时候lastRow的价值是多少?这是1245

Do you have a Worksheet named Data in current workbook? ah... I had a typo in the sheet name. Accidentally typed "Daata".

您是否在当前工作簿中有一个名为Data的工作表?啊...我的表名中有一个拼写错误。意外打字“Daata”。

I always find the VBA error 'Subscript out of Range' sends me down the wrong track as it immediately makes me look for a numeric subscript being out of range. However in this case it's just that the named Sheet cannot be found.

我总是发现VBA错误'Subscript out of Range'让我走错了轨道,因为它立刻让我找到一个超出范围的数字下标。但是在这种情况下,只是找不到命名的Sheet。