为什么我的VBA代码不追加数据，而是替换工作簿中的数据

Question

Dim lastrow As Long, lastrow2 As Long
Dim wksSource As Worksheet, wksDest As Worksheet
Dim source1 As Range, target1 As Range, source2 As Range, target2 As Range
Set wksSource = Workbooks("2021 Tracker.xlsm").Worksheets("Sheet3")
Set wksDest = Workbooks("Jan Tracker).xlsm").Worksheets("Sheet1")
lastrow = wksSource.Cells(Rows.Count, 1).End(xlUp).row
lastrow2 = wksDest.Cells(Rows.Count, 1).End(xlUp).Offset(1,0).row
Set source1 = wksSource.Range("A2:A" & lastrow)
Set source2 = wksSource.Range("B2:B" & lastrow)
Set target1 = wksDest.Range("E" & lastrow2)
Set target2 = wksDest.Range("F" & lastrow2)
source1.Copy: target1.PasteSpecial Paste:=xlPasteValues
source2.Copy: target2.PasteSpecial Paste:=xlPasteValues

此代码将替换目标工作簿的E列和F列中的数据，但我希望将其附加到其中。请提供帮助。

Answer 1

您的代码确定目标工作表A列中的下一行：lastrow2=wksDest。单元格(Rows.Count，1(。结束(xlUp(。偏移(1,0(。一行但是您正在粘贴到列E和F。因此，列A中的最后一行不会更改，这会导致重写。

我重新编写了您的代码，使其更加透明。我认为这种语法会让你更容易发现你所问的错误。这可能需要更多的时间来设置，但时间投入得很好。

Sub AppendData()
Dim wksSource As Worksheet                  ' Source sheet
Dim wksTarget As Worksheet                  ' Target sheet
Dim Source1 As Range
Dim Target As Range
Dim Rl As Long                              ' last row
Set wksSource = Workbooks("2021 Tracker.xlsm").Worksheets("Sheet3")
With wksSource
Rl = .Cells(.Rows.Count, "A").End(xlUp).Row
Set Source = .Range(.Cells(2, "A"), .Cells(Rl, "B"))
End With
Set wksTarget = Workbooks("Jan Tracker).xlsm").Worksheets("Sheet1")
With wksTarget
Set Target = .Cells(.Rows.Count, "E").End(xlUp).Offset(1)
End With
Source.Copy Deestination:=Target
Application.CutCopyMode = False
End Sub