VBA to Consolidate a few Workbooks into 1 Workbook

[MICowboy13]

I'm trying to create a macro which does this:

Allows me to choose a few workbooks (usually about 4-5 but could be anywhere from 2-15) from a server and consolidate them into one workbook. Each workbook needs to be a seperate sheet.
Each workbook consists of 2 sheets however only the 1st sheet will be needed to transfer.
At the end I would like to be able to create a summary sheet. (all of the previous sheets onto one summary sheet at the end)

Other notes:
These files all have different names so i have to be able to choose which files i want consolidated.
I'm running excel 2010.
I would like to be able to keep the same formatting as the sheet im transfering.

So far... I was able to find this macro. The problem with this is that it puts all workbooks selected on one sheet, and for some reason it wraps the text.

Please Login or Register  to view this content.

Any help would be greatly appreciated.

Thanks.

[MICowboy13]

I was able to find this code which does everything except it doesn't allow me to select just the first sheet of the workbook. How do i do this?

Please Login or Register  to view this content.

[JBeaucaire]

Instead of this:

Please Login or Register  to view this content.

Use this:

Please Login or Register  to view this content.

[MICowboy13]

Perfect! Thank you so much!

If anyone could help me add a summary sheet at the end of the macro, it would be greatly appreciated. Or if this is not possible please let me know and I can create a second macro.

[JBeaucaire]

"Summary sheet" could mean anything. If you want to post an example workbook showing a few imported sheets and a manual mockup of what the summary would be showing, we can help.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

[MICowboy13]

Sorry, by summary i mean putting all the info that i just imported into the first sheets into one "summary" at the end

[JBeaucaire]

'MANY SHEETS TO ONE SHEET
Here's a macro for merging data from multiple sheets into a "consolidation" sheet where the source sheets are the same layout.

Sheets to One Sheet - Part 1

There's a sample file there, too, to make it easy to try it out.

[MICowboy13]

Thank you, you have been a huge help.

Quick question, can i add this second macro to the first so that only one macro has to be ran to do all of this at one time?

Thanks again!

[JBeaucaire]

You can simply call the macro at the end of yours.

Please Login or Register  to view this content.