transpose across workbooks

Hi everyone whos knowledge of excel I wish I had,

I hesitated to post as I have so far sound some similar posts that I am about to make, and macros that could come close but I just don't have the knowledge to de-bug and tweak for my own purposes.

The challenge, I need to convert the first column of data in a large amount of workbooks with multiple sheets(tabs) into one workbook with multiple tabs. It immediately gets complicated when the column range varies for each set of workbooks. It will always begin at A7 for every sheet in every workbook, but some columns are shorter than others. Now each workbook with multiple tabs needs to have the data in column A copied and transposed (turn it from column into a row) to another "merged sheet" inside of the "merged workbook" which will contain one tab per set of workbooks. And each row in the tabs of the final workbook ("merged workbook") will have the data from each set of input workbooks. So workbook set1 = tab1 inside the merged workbook. This may likely mean that a seperate macro or formula or process is needed for each set, which is totally fine because it cuts so much manual tedium.

I hope I'm clear so far and I hope this is possible, when I say workbook sets, it's just 4 or 5 workbooks (with multiple tabs) copied many times, but the data contained within them all is unique. In the merged workbook the first row will be pre-poulated with headings ready for the data to be slotted into row2 and below. Now if I may confuse you more, the data from each tab from the input workbooks needs to be continuous when it is displayed in the merged workbook. i.e. one long continuous row of data where cells A2 to H2 would all be from tab1 of an input sheet, I2 to M2 would be the data from the next tab. And so on and so forth. One row for one inputsheet, one tab for one set of inputsheets.

If you've read this far thank you very much and I am a confused fellow so I'll welcome any questions to clarify what I'm after If I've confused some of you helpful sorts. Much longer post than I anticipated. THE END

Welcome to the forum IcedMrT,

Too many words mate!

Is it possible to post a sample of the Master sheet and one of the sheets you want to pull data from.

That way, we might get a better image of what you need.

Regards,

David

Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.
When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Heya David A Coop.

I know, sorry bout all the words . Helped to write it all down (for me at least :p). I've attached some examples of the input and desired output.

It's worth noting a few things different in the examples, firstly I will be doing this in EXCEL 2007. Secondly the steps are not actually numbered in the real version so I don't expect the steps to somehow order themselves in the final output, and that's totally fine. The type of input workbook is actually .xlsm and yes the tab names don't have any relation to them. Hope I didn't miss out anything there.

MasterSheet.xlsm

I have attached a sample macro.

When you start it, you will be asked to identify the file from which you will get the data. Once you select the file, it will be copied across the the Master Sheet tab you are currently on.

Regards,

David



When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Hi,

Change the MasterSheet.xlsx to MasterSheet.xlsm.

When I was testing, it didn't seem to matter, but I had the save the workbook as macro enabled, and didn't think to change the code.

DAC