Link to home
Start Free TrialLog in
Avatar of DougDodge
DougDodge

asked on

Excel VBA Append Data From Closed Workbook to Active Workbook/worksheet

I am trying to figure out a VBA macro to allow user to open a closed workbook, with the aid of FileOpen, select Sheet 1 which has data from range A7:G last row. Evaluate first row date to append the data to the active sheet in the first workbook without overwriting current data.
The opening of two workbooks and copy paste is taking too long considering there is one master and several workbooks to import from on a daily basis.
Avatar of Professor J
Professor J

Sub Workbook_test()
    Dim wb As Workbook
    Dim rng As Range
    Dim lastRow As Long
    ' turn off the screen updating
    Application.ScreenUpdating = False
    Set wb = Workbooks.Open("C:\FOLDERNAME\yourfile.xls", True, True)
    'range of data from the source workbook
    Set rng = wb.Worksheets("sheet1").Cells(.Rows.Count, "A").End(xlUp).Row + 1
    'the sheet in this workbook to copy to
    With ThisWorkbook.Worksheets("sheet1")
        'find last used row
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        'copy range to it
        rng.Copy .Range("A" & lastRow)
    End With
    ' close the source workbook without saving any changes
    wb.Close False
    ' free memory
    Set wb = Nothing
    ' turn on the screen updating
    Application.ScreenUpdating = True
End Sub

Open in new window

Avatar of DougDodge

ASKER

Sorry, the code does not seem to work. It issues an "Invalid or unqualified Reference"

It hangs up here:

 Set rng = wb.Worksheets("sheet1").Cells(.Rows.Count, "A").End(xlUp).Row + 1
ASKER CERTIFIED SOLUTION
Avatar of Glenn Ray
Glenn Ray
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks.... A couple of slight changes and it works perfectly.
You're welcome; I'm glad I could help.

-Glenn