Copy Data from one Excel Workbook to Another Workbook in Last row

Question

I am Trying to run this Code, which will copy the Source sheet Row to Destination Sheet last Row, but my this code giving error 400 while compiling,

Advance Thanks for Help

Sub CopyData()
    Dim sBook_t As String
    Dim sBook_s As String

    Dim sSheet_t As String
    Dim sSheet_s As String

    On Error GoTo Errorcatch

    sBook_t = "C:\Users\Unknown\Desktop\Free\Calculators.xlsx"
    Workbooks.Open (sBook_t)
    sBook_s = "C:\Users\Unknown\Desktop\Free\PRODUCT_35.xlsm"
    Workbooks.Open (sBook_s)

    sSheet_t = "cstdatalist"
    sSheet_s = "cstdata"

    Sheets(sSheet_s).Range("A2").Copy Destination:=Sheets(sSheet_t).Range("A2")
End Sub

You can find the last row and then copy the data there. [Here](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba/11169920#11169920) the code to find last row. — Siddharth Rout, Mar 13 '19 at 07:49

score 0 · Answer 1 · answered Mar 13 '19 at 07:57

Have a try on following sub.

Sub CopyData()
Dim wb As Workbook
Dim sht, shtLocal As Worksheet
Dim rngPaste As Range
Dim rngLastData, wbPath As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False

    wbPath = "D:\dBook.xlsx"
    Set wb = Workbooks.Open(wbPath)
    Set sht = wb.Sheets(1)
    Set shtLocal = ThisWorkbook.Sheets("Sheet1")
    Set rngPaste = sht.Cells(Rows.Count, 1).End(xlUp).Offset(1) 'Destination range set after last used cell of column A

    rngLastData = shtLocal.Cells(Rows.Count, "A").End(xlUp).Address
    shtLocal.Range("A1:" & rngLastData).Copy rngPaste

    wb.Save
    wb.Close
    Set sht = Nothing
    Set shtLocal = Nothing
    Set rngPaste = Nothing

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Hay this Code is not working for Me on MS Excel, i have done this on Google Sheet but stuck in Microsoft Excel, Below is my Google Sheet Code ' function CopyData(){ var sss = SpreadsheetApp.openById('10Grh3DBL4sU'); var ss = sss.getSheetByName('Database'); var SRange = ss.getRange("A3:EO3"); var SData = SRange.getValues(); var tss = SpreadsheetApp.openById('17gXnrJUV5_qw'); var ts = tss.getSheetByName('CalDatabase'); ts.getRange(ts.getLastRow()+1,1,SData.length,SData[0].length).setValues(SData); }' @harun24hr — Bahadur Ali, Mar 14 '19 at 08:41

score 0 · Answer 2 · answered Mar 13 '19 at 08:12

enter code hereHere is my adjustment of your code. What I did is declared the workbooks and the worksheets separately. This way it is clear which workbook/sheet is the source and which is the destination.

  Sub CopyData()
        Dim sBook_t As String
        Dim sBook_s As String
        Dim workbook_t As Workbook
        Dim sSheet_t As Worksheet
        Dim sSheet_s As Worksheet


    Dim sSheet_t As String
    Dim sSheet_s As String

    On Error GoTo Errorcatch

    sBook_t = "C:\Users\Unknown\Desktop\Free\Calculators.xlsx"
      set workbook_t =  Workbooks.Open (sBook_t)
    sBook_s = "C:\Users\Unknown\Desktop\Free\PRODUCT_35.xlsm"
      set workbook_s =  Workbooks.Open (sBook_s)

   set sSheet_t = workbook_t.Sheets("cstdatalist")
   set sSheet_s = workbook_s.Sheets("cstdata")

    sSheet_s.Range("A2").Copy Destination:=sSheet_t.Range("A2")


    End Sub

Hello, Thanks for the Help but this code not working still Label not defined error — Bahadur Ali, Mar 14 '19 at 08:36
This is the line that is causing you the issue, `On Error GoTo Errorcatch`. Do you have an error handling label defined? In not try removing this line. — Yane, Mar 14 '19 at 10:50

Copy Data from one Excel Workbook to Another Workbook in Last row

2 Answers2