使用单元格引用复制/粘贴数据(VBA)

I'm trying to copy data from one file into another. First, I'd like to find the last row/column in the source file and use that information to copy the data. However, I can't seem to get the code to copy/paste with the references to the last row and column used. Here's what I currently have:

我试图把数据从一个文件复制到另一个文件。首先，我希望找到源文件中的最后一行/列，并使用该信息复制数据。但是，我似乎无法让代码复制/粘贴到所使用的最后一行和列的引用。以下是我目前的情况:

Dim G7_Tab_Name As String
   G7_Tab_Name = "G7"
Dim wkbDest As Workbook
   Set wkbDest = ActiveWorkbook
Dim SheetToCopy As Worksheet

LastInputRow = wkbSource.Sheets(1).Cells(Cells.Rows.Count, "A").End(xlUp).Row
LastInputColumn = wkbSource.Sheets(1).Cells(1, Columns.Count).End(xlToLeft).Column
Set SheetToCopy = wkbSource.Sheets(1)
CopyRange = Range(SheetToCopy.Cells(LastInputRow, 1), SheetToCopy.Cells(1, LastInputColumn))

'Check if any existing data
If Len(wkbDest.Sheets(G7_Tab_Name).Range("A1").Value) > 0 Then
    wkbDest.Sheets(G7_Tab_Name).UsedRange.ClearContents
    CopyRange.Copy _
    Destination:=Range(wkbDest.Sheets(G7_Tab_Name).Cells(LastInputRow, 1), Cells(1, LastInputColumn))
    Else
       CopyRange.Copy _
       Destination:=Range(wkbDest.Sheets(G7_Tab_Name).Cells(LastInputRow, 1), Cells(1, LastInputColumn))
End If

I'm pretty sure that I'm not capturing the correct range in my CopyRange definition, but I don't know why. In this case, lastRow=27 and lastColumn=13. So, I thought that the range would define as "A1:M27".

我很确定我没有在CopyRange定义中捕获正确的范围，但是我不知道为什么。在本例中，lastRow=27和lastColumn=13。因此，我认为这个范围可以定义为“A1:M27”。

I'm defining these ranges as such because I need to loop through multiple files and copy data from the first sheet in each. This code will be included in a larger For loop to hit each file (once I can get it to copy/paste!)

我正在定义这些范围，因为我需要循环遍历多个文件，并从每个文件中复制数据。这个代码将被包含在一个更大的For循环中，以打击每个文件(一旦我可以将它复制/粘贴!)

1 个解决方案

#1

Whenever you use Cells(), Range(), Columns(), Rows(), etc. make sure you let VBA know what worksheet you expect that to be used on, otherwise , it 'll use the ActiveSheet, which can cause errors. You did this in some places, but not others. Below I've done a mix of using With statements to make life easier for those sheets with long names. Read through the below and see if you can see what I did. Let me know if you have any questions!

每当您使用单元格()、Range()、Columns()、Rows()等时，请确保让VBA知道您希望在哪些工作表上使用它，否则，它将使用ActiveSheet，这会导致错误。你在某些地方这样做了，但其他地方没有。下面我将使用语句和语句组合在一起，使长名称的表单更容易使用。阅读下面的内容，看看你是否能看到我所做的。如果你有任何问题，请告诉我!

Sub t()

Dim G7_Tab_Name As String
G7_Tab_Name = "G7"
Dim wkbDest As Workbook
Set wkbDest = ActiveWorkbook
Dim SheetToCopy As Worksheet
Set SheetToCopy = wkbSource.Sheets(1)

With sheetToCopy
    LastInputRow = .Cells(.Cells.Rows.Count, "A").End(xlUp).Row
    LastInputColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With

CopyRange = SheetToCopy.Range(SheetToCopy.Cells(LastInputRow, 1), SheetToCopy.Cells(1, LastInputColumn))

'Check if any existing data
Dim destWS  As Worksheet
If Len(wkbDest.Sheets(G7_Tab_Name).Range("A1").Value) > 0 Then
    wkbDest.Sheets(G7_Tab_Name).UsedRange.ClearContents
    Set destWS = wkbDest.Sheets(G7_Tab_Name)
    CopyRange.Copy Destination:=destWS.Range(destWS.Cells(LastInputRow, 1), destWS.Cells(1, LastInputColumn))
Else
    CopyRange.Copy Destination:=destWS.Range(destWS.Cells(LastInputRow, 1), destWS.Cells(1, LastInputColumn))
End If

End Sub

You did this in places, but not everywhere. I.E:
LastInputRow = wkbSource.Sheets(1).Cells(Cells.Rows.Count, "A").End(xlUp).Row

你在一些地方这样做，但不是在所有地方。我。艾凡:LastInputRow = wkbSource.Sheets(1).Cells(Cells.Rows。统计,“A”)指标(xlUp).Row最终

You correctly told the first Cells() which sheet to use, but VBA does not "remember" anything, so the next Cells.Rows.Count will be on the Active Sheet, which isn't necessarily your wkbSource.Sheets(1) sheet. Change that line to

LastInputRow = wkbSource.Sheets(1).Cells(wkbSource.Sheets(1).Cells.Rows.Count, "A").End(xlUp).Row

您正确地告诉了第一个单元格()使用哪个表，但是VBA不“记住”任何东西，因此下一个单元格。Count将出现在活动表中，这并不一定是您的wkbSource.Sheets(1)表。将该行改为LastInputRow = wkbSource.Sheets(1). cells (wkbSource.Sheets(1). cells.rows。统计,“A”)指标(xlUp).Row最终

(Note: This may not solve your issue, but it will absolutely help tighten up the code and leave room for fewer errors).

(注意:这可能不能解决您的问题，但它将绝对有助于加强代码并为更少的错误留出空间)。

Edit: Try replacing your CopyRange with this:

编辑:试着用以下内容替换你的文案范围:

With sheetToCopy
copyRange = .Range(.Cells(1, 1), .Cells(LastInputRow, LastInputColumn))
End With

This will make a range from A1 to the last row in the last column.

这将使范围从A1到最后一列的最后一行。

Edit2: It's not clear from the code you posted how you declared CopyRange, but you should do it like this:

你发布的代码不清楚你是如何声明CopyRange的，但是你应该这样做:

Dim CopyRange as Range
Set CopyRange = .Range(.Cells(1, 1), .Cells(LastInputRow, LastInputColumn))