Why is my VBA code not appending data but instead replacing data in the workbook?

%3CLINGO-SUB%20id%3D%22lingo-sub-1250836%22%20slang%3D%22en-US%22%3EWhy%20is%20my%20VBA%20code%20not%20appending%20data%20but%20instead%20replacing%20data%20in%20the%20workbook%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1250836%22%20slang%3D%22en-US%22%3E%3CPRE%3E%3CSPAN%20class%3D%22kwd%22%3EDim%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20lastrow%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22kwd%22%3EAs%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22kwd%22%3ELong%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20lastrow2%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22kwd%22%3EAs%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22kwd%22%3ELong%3C%2FSPAN%3E%0A%0A%3CSPAN%20class%3D%22kwd%22%3EDim%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20wksSource%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22kwd%22%3EAs%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20Worksheet%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20wksDest%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22kwd%22%3EAs%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20Worksheet%0A%0A%3C%2FSPAN%3E%3CSPAN%20class%3D%22kwd%22%3EDim%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20source1%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22kwd%22%3EAs%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20Range%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20target1%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22kwd%22%3EAs%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20Range%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20source2%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22kwd%22%3EAs%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20Range%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20target2%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22kwd%22%3EAs%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20Range%0A%0A%3C%2FSPAN%3E%3CSPAN%20class%3D%22kwd%22%3ESet%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20wksSource%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20Workbooks%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22str%22%3E%222021%20Tracker.xlsm%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E).%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3EWorksheets%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22str%22%3E%22Sheet3%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E)%3C%2FSPAN%3E%0A%0A%3CSPAN%20class%3D%22kwd%22%3ESet%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20wksDest%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20Workbooks%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22str%22%3E%22Jan%20Tracker).xlsm%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E).%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3EWorksheets%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22str%22%3E%22Sheet1%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3Elastrow%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20wksSource%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E.%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3ECells%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3ERows%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E.%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3ECount%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%2C%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22lit%22%3E1%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E).%3C%2FSPAN%3E%3CSPAN%20class%3D%22kwd%22%3EEnd%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3ExlUp%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E).%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3Erow%0A%0Alastrow2%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20wksDest%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E.%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3ECells%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3ERows%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E.%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3ECount%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%2C%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22lit%22%3E1%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E).%3C%2FSPAN%3E%3CSPAN%20class%3D%22kwd%22%3EEnd%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3ExlUp%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E).%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3EOffset%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22lit%22%3E1%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22lit%22%3E0%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E).%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3Erow%0A%0A%3C%2FSPAN%3E%3CSPAN%20class%3D%22kwd%22%3ESet%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20source1%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20wksSource%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E.%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3ERange%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22str%22%3E%22A2%3AA%22%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22pun%22%3E%26amp%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20lastrow%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E)%3C%2FSPAN%3E%0A%0A%3CSPAN%20class%3D%22kwd%22%3ESet%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20source2%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20wksSource%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E.%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3ERange%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22str%22%3E%22B2%3AB%22%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22pun%22%3E%26amp%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20lastrow%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E)%3C%2FSPAN%3E%0A%0A%3CSPAN%20class%3D%22kwd%22%3ESet%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20target1%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20wksDest%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E.%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3ERange%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22str%22%3E%22E%22%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22pun%22%3E%26amp%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20lastrow2%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E)%3C%2FSPAN%3E%0A%0A%3CSPAN%20class%3D%22kwd%22%3ESet%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20target2%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20wksDest%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E.%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3ERange%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22str%22%3E%22F%22%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22pun%22%3E%26amp%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20lastrow2%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3Esource1%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E.%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3ECopy%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%3A%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20target1%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E.%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3EPasteSpecial%20Paste%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%3A%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3ExlPasteValues%0A%0Asource2%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E.%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3ECopy%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%3A%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3E%20target2%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E.%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3EPasteSpecial%20Paste%3C%2FSPAN%3E%3CSPAN%20class%3D%22pun%22%3E%3A%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22pln%22%3ExlPasteValues%3C%2FSPAN%3E%3C%2FPRE%3E%3CP%3EThis%20code%20replaces%20data%20in%20columns%20E%20and%20F%20of%20destination%20workbook%2C%20but%20i%20want%20it%20to%20append%20to%20it.%20Please%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1250836%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1251185%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20is%20my%20VBA%20code%20not%20appending%20data%20but%20instead%20replacing%20data%20in%20the%20workbook%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1251185%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F593699%22%20target%3D%22_blank%22%3E%40satvikzwarrior%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20maybe%20the%20lastrow2%20as%20you%20are%20trying%20to%20find%20the%20last%20row%20based%20on%20column%20A%20in%20the%20destination%20worksheet%20and%20if%20there%20is%20no%20data%20in%20column%20A%20or%20the%20number%20of%20rows%20with%20data%20in%20column%20A%20is%20less%20than%20number%20of%20rows%20with%20data%20in%20column%20E%20in%20destination%20workbook%2C%20the%20paste%20method%20may%20replace%20the%20data%20in%20column%20E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20to%20find%20the%20lastrow2%20based%20on%20column%20E%20in%20the%20destination%20worksheet.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Elastrow2%20%3D%20wksDest.Cells(Rows.Count%2C%20%22E%22).End(xlUp).Offset(1%2C%200).Row%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor
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

This code replaces data in columns E and F of destination workbook, but i want it to append to it. Please help.

1 Reply
Highlighted

@satvikzwarrior 

The problem maybe the lastrow2 as you are trying to find the last row based on column A in the destination worksheet and if there is no data in column A or the number of rows with data in column A is less than number of rows with data in column E in destination workbook, the paste method may replace the data in column E.

 

Try to find the lastrow2 based on column E in the destination worksheet.

lastrow2 = wksDest.Cells(Rows.Count, "E").End(xlUp).Offset(1, 0).Row