Combine 40 workbooks into 1 with 40 sheets

[CarysW]

Good morning.

I have 40 workbooks, all different names with different data. I would like to have them all in 1 workbook in 40 worksheets.
All of the workbooks are in 1 folder, the new workbook will be newly created.

Is there some code to do this?

Thanks muchly

 

[JamesMcS]

I'd have thought it would probably just be quicker to copy and paste the sheets into your new workbook...

 

[Rx_]

Searching the forum last week, there was an example that might help.

Set a reference to the workbooks.
Look for code For Each Workbook
then once the workbook is referenced
for each worksheet

Seem to remember that the Microsoft Excel VBA help had a example of this in the Excel Object Heirachry section.

It has been a couple of years since doing this, but it is easier to find the code than copying 40 sheets if it is an every day task.

If you have ten workbooks open and wanted to build an array or list box to hold the names, the code would look something like this:

Dim wkb As Workbook

For Each wkb In Workbooks
If Windows(wkb.Name).Visible Then _
ListBox1.AddItem wkb.Name
Next


At the ListBox1 - add code to open the workbook, select the Current Region, copy it, then past it into a Worksheet(X)

With these keywords, you might find some good examples.