VBA-在不粘贴正确位置的床单之间复制和粘贴

Question

我正在使用vba将一些数据从一张纸粘贴到另一张。

第一个按钮按旨在设计(更新一行i45等(。

。

第二个按钮压力是将数据粘贴到下一行(i46等(，因为它是空行(，但实际上它更新了行i21任何后续按下只需更新i21覆盖。

您可以检查我做错了什么。

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Set copySheet = Worksheets("Raw")
Set pasteSheet = Worksheets("Week Data (all)")
copySheet.Range("H15:AA15").Copy
pasteSheet.Cells(45, 9).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Answer 1

那是因为使用pasteSheet.Cells(45, 9)，您始终在第45行开始搜索。相反，您应该在.Rows.Count处启动搜索，这是列中的绝对最后一行(即1048576(。因此，尝试...

With pasteSheet
    .Cells(.Rows.Count, 9).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End With

Answer 2

也是如此。

Private Sub CommandButton1_Click()
Dim vDB
Dim rngT As Range
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
    Application.ScreenUpdating = False
    Set copySheet = Worksheets("Raw")
    Set pasteSheet = Worksheets("Week Data (all)")
    vDB = copySheet.Range("H15:AA15")
    Set rngT = pasteSheet.Cells(Rows.Count, 9).End(xlUp)(2)
    rngT.Resize(UBound(vDB, 1), UBound(vDB, 2)) = vDB
    Application.ScreenUpdating = True
End Sub