Combining sheets into one workbook

[Malachiuri]

Hey folks,

Im sure this is brought up a lot and I apologize for reposting but I cant for the life of me find how to do this:

We have hundreds of separate spreadsheets tracking equipment. I want to combine them all into just a few workbooks with multiple pages, one page per item we are tracking.

I have been having to open each excel file, copy it over to the master workbook page etc and its taking forever.

Is there a way to import multiple single page spreadsheets into one workbook, one sheet per page, en mass?

Thanks all,

Mark

 

[Trevor G]

Welcome to the forum Mark,

Would all the workbooks be in a single folder? Which version of MS Office are you using.

Yes this has been covered before, but you might have something unique to ask or gather.

 

[Malachiuri]

We are using 2010...

Let me explain further, sorry.

We have 150 folders, with around 100 separate Excel files per folder(one per equipment item)

I want to condense them down to say, 10 folders, with a master excel workbook in each, with a page per equipment item to be tracked.

Thanks

 

[Trevor G]

Does each workbook have exactly the same sheet names? This will help.

Are all the folders in a main folder or spread around in different folders, this will either make it easier or not.

 

[Malachiuri]

The name of each sheet is the serial number of the item. Im hoping that when I can combine, the name will populate to the page tab in the workbook.

I dont think I can do one mass import, Im assuming because each equipment type has its own tracking needs that I will have to do one import per equipment type workbook. All of the individual worksheets we have now are at least based on a master template for each equipment type so they are uniform in that respect.

The folders are a mess atm. Some are folders inside folders.

I dont expect this to be fast fast, Im unscrewing a mess for my wife here, just quicker than what Im doing...

 

[Trevor G]

Look at this link perhaps it can help start you off.

You will have to copy each part as it instructs you into a single module sheet then run the code CombineFiles

I tested it and it worked ok, the problem you will have is how many workbooks are in a folder? And How many folders do you have.

You might have to run this in several different ways to get your solution but it should be achievable.

http://vbaexpress.com/kb/getarticle.php?kb_id=829

 

[Malachiuri]

Durnit Im being needlessly complicated.

Lets assume I have a folder with 10 excel spreadsheets in it. Each is based on a master template with identical format. Each sheet is named for the item its tracking(truck, car, apple, orange, etc)

I want to easily and quickly combine them all into one workbook, with a page per tracked item.

That seems a bit more clear. Sorry.

 

[Malachiuri]

Look at this link perhaps it can help start you off.

You will have to copy each part as it instructs you into a single module sheet then run the code CombineFiles

I tested it and it worked ok, the problem you will have is how many workbooks are in a folder? And How many folders do you have.

You might have to run this in several different ways to get your solution but it should be achievable.

http://vbaexpress.com/kb/getarticle.php?kb_id=829

This worked brilliantly!

Only thing it did not do is to name each page tab the original sheet name, but I can do that manually.

Thanks!!