Consolidate all worksheets into master workbook

jeffreybrown

Well-known Member
Joined
Jul 28, 2004
Messages
5,152
Hi All,

I think this needs a For Each loop, but I am stuck on how to get it accompished. In the folder I will have about 26 workbooks with all different titles, but the beggining is always the same for the workbook and the worksheet (Bldg). There will be only one ws per wb and the wb I am using to collect all of the worksheets is Final.xls. The code attached works fine, but the only way right now I know how to capture the next wb/ws is with a call. Can somebody help with how to loop through all wbs and return the ws?

Code:
Sub CopyAllWrksht()
    Dim MyPath As String, MyFile As String
    On Error GoTo Error_Handler
        MyPath = ActiveWorkbook.Path & "\"
        MyFile = "Bldg LBV.xls"
    If Dir(MyPath & MyFile) = Empty Then
        MsgBox "The file " & MyFile & " was not found", , "File Doesn't Exist"
    Exit Sub
    End If
        Workbooks.Open Filename:=MyPath & MyFile
        Sheets("Bldg LBV").Copy After:=Workbooks("Final.xls").Sheets(1)
        Workbooks(MyFile).Close
        Exit Sub
Error_Handler:
End Sub
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Hi,

Code:
Sub CopyAllWrksht()
Dim MyPath As String, MyFile As String
Dim DestFile As Workbook, sWB As Workbook
Application.ScreenUpdating = 0
Set DestFile = Workbooks("Final.xls")
On Error GoTo Error_Handler
    MyPath = ActiveWorkbook.Path & "\"
    MyFile = Dir(MyPath & "*.xls")
Do While MyFile <> ""
    Set sWB = Workbooks.Open(Filename:=MyPath & MyFile, UpdateLinks:=0)
    sWB.Sheets("Bldg LBV").Copy After:=DestFile.Sheets(1)
    sWB.Close False
    MyFile = Dir()
Loop
Error_Handler:
Application.ScreenUpdating = 1
End Sub
 
Upvote 0
Hi Kris,

Thank you for your time. When I ran this all it did was open one of the two workbooks I would like to consolidate into the master workbook, but it didn't do anything but open. Right now for testing I have two workbooks in my folder (Bldg LBV and Bldg1) and both have just one sheet which is named the same as the workbook name. Again this macro you provided just opened Bldg 1 wb, but that's it. Any other suggestions?
 
Upvote 0
Jeff

I've not tested Kris's code and I'm just off to watch the second rugby match.

But I would suggest you remove the On Error... stuff.

That could just be hiding errors an skipping code.:)

Then try stepping through the code with F8 to see what is going on.

There is definitely code in what's been posted for copying a worksheet and closing workbook.
 
Upvote 0
Hi,

Open VBE window and run the code manually( hit F8) and see what happens for each line.

In the meantime replace
Code:
sWB.Sheets("Bldg LBV").Copy After:=DestFile.Sheets(1)

with

Code:
sWB.Sheets(1).Copy After:=DestFile.Sheets(1)
 
Upvote 0
That's exactly it Kris. Using F8 the error was on the line you indicated and by changing it to your suggestion it works great. Thanks so much. Thanks to you to Norie. Hope the rugby match is good.
 
Upvote 0
That's exactly it Kris. Using F8 the error was on the line you indicated and by changing it to your suggestion it works great. Thanks so much. Thanks to you to Norie. Hope the rugby match is good.

You are welcome! (y)
 
Upvote 0
Kris,

Actually there is one more problem. Since the Final.xls workbook is in the same folder the loop wants to open Final.xls even though it is already open so I get the warning, Final.xls is already open. Reopening will cause any changes...Yes or No. How can this be prevented since Final.xls is already open?
 
Upvote 0
Kris,

Sometimes I don't use my brain very well. Since all the wbs begin with a B I just changed
Code:
MyFile = Dir(MyPath & "*.xls")
To
Code:
MyFile = Dir(MyPath & "B*.xls")
 
Upvote 0
Hey!

I am new to using macros in excel, but I think the issue I am trying to solve is similar to the one posted here.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> </o:p>
Every month I will be receiving a data file from each of the fourteen entities which I am monitoring. In each data file there are a set of worksheets containing different information (e.g. one sheet for the Profit & Loss, one sheet for the Balance Sheet, etc). The reporting format is standardized for all the entities so that each data file and the worksheets contained within are exactly the same. (and have the same file and tab names)
<o:p> </o:p>
What I need to do is to compile a consolidated report for the fourteen entities, which will sum up the data in each cell and present this as the total for the whole organization in one file.
<o:p> </o:p>
Couple of additional challenges:
- I only need the data summed up for a sub-set of the sheets in each data file (e.g. out of a total of 10 sheets I will only need to have 5 of the sheets consolidated)
- From the newly created consolidated data I would also be calculating some ratios etc on new sheets, ideally I could set up the spreadsheet so that these are calculated automatically once the data is consolidated and presented on separate worksheets
<o:p> </o:p>
Thanks!!!
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,752
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top