Macro to transfer data from 1 excel to another

Question

I am new to VBA Macro. I created a macro that transfers data from one excel file to another. However, I have already a data present in other excel the macro should paste the data below the existing data. But my codes are replacing the existing data which I don't want to. Below are my codes and some screenshots.

Below is the destination excel file where the data is transferred to. Now, there is already a data present in there, so my macro should paste or transfer the data below the existing data. However, my macro replaced the existing data which I don't want.. Please help me on this................

My Codes: -

Dim shSF As Worksheet, sh1 As Worksheet
Dim wb As Workbook, LastRow As Long
Dim wt As Worksheet
Dim i As Long


 Set shSF = Worksheets("Sheet2")
 
 
 LastRow = shSF.Range("A" & Rows.Count).End(xlUp).Row

 Set wb = Workbooks.Open("C:DestinationPath.xlsm")
 Set sh1 = wb.Worksheets("Sheet2")
 
 shSF.Range("A1:C" & LastRow).Copy Destination:=sh1.Range("A1")
 
 Set wt = Worksheets("Sheet1")

 wt.Range("A2:B" & LastRow).ClearContents


 wb.Save

End Sub

you already know how to find the last row, just find the last row on the target sheet and paste one row below that. — Scott Craner
I am very much confused on this, please help me show in the code exactly that last row should be please — Akshay Chari

YasserKhalil YasserKhalil · Accepted Answer · 2020-09-09T15:40:08

Change to these lines

Set sh1 = wb.Worksheets("Sheet2")
Dim lr As Long
lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row + 1
shSF.Range("A1:C" & LastRow).Copy Destination:=sh1.Range("A" & lr)

Macro to transfer data from 1 excel to another

1 Answers