Please help! Consolidate multiple seperate workbooks into one master workbook

[iceplant]

Hi Everyone!

This is my first time to post a topic here! Thanks for all the help in advance. This is a topic I guess that have been disccused before.

My major question is how to consolidate multiple seperate workbooks into one master workbook. I am okay with VBA code or add-in tool or any other methods.

Excel template has two tabs.
- Raw data, which is exported from the system periodically
- Summary, which vlookup raw data plus input other hard coded information

I need to send out this template to each region owner(column G) to fill out the manual input information(highlight in blue) in the summary tab. After they send back their pieces to me, I usually copy/paste to my master worksheet, which all has the same format.

Is there a quick way to do the consolidation?

Thanks for your help in advance.

 

[Deepak]

I think you are looking something like this..

Option Explicit

Sub consolidate_wb()
Dim swb As Workbook, owb As Workbook, lr As Long
Dim ws As Worksheet, ows As Worksheet, rng As Range

Set ws = ActiveSheet
For Each swb In wb.Parent.Path
    Set owb = Workbooks.Open(swb)
        Set ows = owb.Sheets("Summary")
            Set rng = ows.Union(Range([E4:G26]), Range([r4:u26]))
            With ws
                lr = .Cells(.Rows.Count, 1).End(xlUp)(2).Row
                rng.Copy .Cells(lr, 1)
            End With
    ows.Parent.Close False
Next
Set rng = Nothing
Set owb = Nothing
Set ws = Nothing
MsgBox "Done", vbInformation
End Sub