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.
DougDodgeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamCommented:
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

0
DougDodgeAuthor Commented:
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
0
Glenn RayExcel VBA DeveloperCommented:
While I suspect your instructions are not complete in what you wish to do, this code will do the following:
* identify the next available cell in the active sheet in which to append data (based on column A)
* prompt the user to open an Excel workbook (presumably to import data from)
* copy the range in the first sheet of that workbook from A7:Glastrow
* paste that copied selection to the original workbook.
* close the import workbook.
Option Explicit
Sub Append_Data()
    Dim strDest As String
    Dim lngLastSourceRow As Long
    Dim strFilePath, strImportFileName As String
    Dim strDestFile, strDestSheet As String
    
    Application.ScreenUpdating = False
    strDestFile = ActiveWorkbook.Name
    strDestSheet = ActiveSheet.Name
    strDest = Range("A2").End(xlDown).Offset(1, 0).Address
    
    strFilePath = Application.GetOpenFilename("Excel Files (*.xls;*.xlsx), *.xls;*.xlsx", , "Open Excel File to Import")
    If strFilePath = "False" Then Exit Sub
    Workbooks.Open (strFilePath)
    strImportFileName = ActiveWorkbook.Name
    Sheets(1).Select 'assumes data to be copied is on the first sheet, regardless of name
    lngLastSourceRow = Range("A" & Cells.Rows.Count).End(xlUp).Row
    Sheets("Sheet1").Range("A7:G" & lngLastSourceRow).Copy
    Workbooks(strDestFile).Sheets(strDestSheet).Range(strDest).PasteSpecial
    Application.CutCopyMode = False
    Workbooks(strImportFileName).Close savechanges:=False
    
    Application.ScreenUpdating = True
End Sub

Open in new window


Regards,
-Glenn
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DougDodgeAuthor Commented:
Thanks.... A couple of slight changes and it works perfectly.
0
Glenn RayExcel VBA DeveloperCommented:
You're welcome; I'm glad I could help.

-Glenn
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.