Excel VBA - to combine data of multiple worksheets from multiple workbooks

Hello All,

I have workbook A, B, C, D etc in a folder...
each workbook has four sheets... sheet 1, sheet2 etc.

I want to combine the data from sheet1 of all workbooks into sheet1 of master workbook (which already exists) and similarly sheet2 etc.

The workbooks are from different users... they all have same headings.

Your help will be much appreciated.
F PatelAsked:
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.

Mike in ITIT System AdministratorCommented:
I believe this should do it for you. I don't have your exact setup so there might still be a few tweaks that need doing.
Sub LoopAllExcelFilesInFolder()
    Dim wb As Workbook, wbMain As Workbook
    Dim myPath As String
    Dim MyFile As String
    Dim myExtension As String
    Dim FldrPicker As FileDialog
    Dim One, Two, Three, Four
    
    Set wbMain = ActiveWorkbook
    'Optimize Macro Speed
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    'Retrieve Target Folder Path From User
    Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
    
    With FldrPicker
        .Title = "Select A Target Folder"
        .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
    End With
    
    'In Case of Cancel
NextCode:
    myPath = myPath
    If myPath = "" Then GoTo ResetSettings
    
    'Target File Extension (must include wildcard "*")
    myExtension = "*.xls*" 'includes .xls, .xlsx, and .xlsm files
    
    'Target Path with Ending Extention
    MyFile = Dir(myPath & myExtension)
    
    'Loop through each Excel file in folder
    Do While MyFile <> ""
    'Set variable equal to opened workbook
    Set wb = Workbooks.Open(Filename:=myPath & MyFile)
    
    'Ensure Workbook has opened before moving on to next line of code
    DoEvents
    
    One = wb.Sheets("Sheet1").Range("I9").Value
    Two = wb.Sheets("Sheet2").Range("J10").Value
    Three = wb.Sheets("Sheet3").Range("H11").Value
    Four = wb.Sheets("Sheet4").Range("A12").Value
    
    Dim LastRow1
    LastRow1 = One.Cells(One.Rows.Count, 1).End(xlUp).Row
    Dim LastRow2
    LastRow2 = Two.Cells(Two.Rows.Count, 1).End(xlUp).Row
    Dim LastRow3
    LastRow3 = Three.Cells(Three.Rows.Count, 1).End(xlUp).Row
    Dim LastRow4
    LastRow4 = Four.Cells(Four.Rows.Count, 1).End(xlUp).Row
    
    wbMain.Sheets("Sheet1").Range("A" & LastRow1).Value = One
    wbMain.Sheets("Sheet2").Range("A" & LastRow2).Value = Two
    wbMain.Sheets("Sheet3").Range("A" & LastRow3).Value = Three
    wbMain.Sheets("Sheet4").Range("A" & LastRow4).Value = Four
    
    'Ensure Workbook has closed before moving on to next line of code
    DoEvents
    
    'Get next file name
    MyFile = Dir
    Loop
    
    'Message Box when tasks are completed
    MsgBox "Task Complete!"
    
ResetSettings:
    'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Open in new window

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
F PatelAuthor Commented:
Thank you Mike

What is the purpose of .range (). value?
0
Mike in ITIT System AdministratorCommented:
To what line are you referring to?

When you are specifying a range it can be a single cell or multiple cells. The ".value" is just looking at the value(s) of the cell(s) specified by what it is attached to whether that is a Renge() object or a Cells() object.
0
TracyVBA DeveloperCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Accept: Mike in IT (https:#a42440363)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

broomee9
Experts-Exchange Cleanup Volunteer
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 Office

From novice to tech pro — start learning today.