Copy and Pasting excel data from unopened excel sheet to destination excel sheet to the last row

Question

I am trying to copy and paste data from one excel sheet to another sheet into the last available row. My current code is overwriting the previous data. I would like to add data to last available row. Please help.

Sub CopyToExcel()

Application.ScreenUpdating = False

Application.EnableEvents = False

WB_1 = ThisWorkbook.Name
WB_2 = "Analysis.xls"
b_file = "C:\\" & WB_2

On Error Resume Next
    Workbooks.Open (b_file)
If Err.Number <> 0 Then
    MsgBox ("Cannot find " & WB_2 & "-file!")
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
End If

t_max = Workbooks(WB_2).Sheets("Page 1").Cells(Rows.Count, 1).End(xlUp).Row
Workbooks(WB_2).Sheets("Page 1").Range("A1:R" & 100).Copy Destination:=Workbooks(WB_1).Sheets("DB2").Range("C1")
Workbooks(WB_2).Close SaveChanges:=False
Workbooks(WB_1).Activate

Application.EnableEvents = True
Application.ScreenUpdating = True
ActiveWorkbook.Save

End Sub

I dont see you finding the last row in WB_1 nor can I see you using that? — Siddharth Rout

Siddharth Rout Siddharth Rout · Accepted Answer · 2017-06-06T08:45:03

I don't see you finding the last row in WB_1 nor can I see you using that?. My suggestion: Work with objects. It becomes easier to understand what you are doing and you have a better control over the code.

Is this what you are trying? (Untested)

Sub CopyToExcel()
    Dim wbDest As Workbook, wbSource As Workbook
    Dim wsDest As Worksheet, wsSource As Worksheet
    Dim lRowDest As Long, lRowSource As Long

    '~~> This is the workbook where you want to copy
    Set wbDest = ThisWorkbook
    '~~> This is the worksheet where you want to copy
    Set wsDest = wbDest.Sheets("DB2")
    '~~> This is the last row where the data will be copied
    lRowDest = wsDest.Range("A" & wsDest.Rows.Count).End(xlUp).Row + 1

    '~~> This is the workbook FROM where you want to copy
    Set wbSource = Workbooks.Open("C:\Analysis.xls")
    '~~> This is the worksheet FROM where you want to copy
    Set wsSource = wbSource.Sheets("Page 1")

    With wsSource
        '~~> This is the last row till where the data will be copied from
        lRowSource = .Range("A" & .Rows.Count).End(xlUp).Row

        '~~> Do the final Copy
        .Range("A1:R" & lRowSource).Copy wsDest.Range("C" & lRowDest)
    End With

    wbSource.Close (False)
End Sub

Copy and Pasting excel data from unopened excel sheet to destination excel sheet to the last row

1 Answers