Ramona
asked on
How to merge multiple spreadsheets in Excel 2003
Hi Experts. I have collected storage performance data for 30 MS Exchange servers. The higher ups want to see it in a spreadsheet. How do I merge these documents into a single spreadsheet? Thank you!
Rhiaanon44,
Ehm, I should probably have said that I am assuming the data is already in 30 sheets in the workbook. If any of this is incorrect, please let me know the details!
Regards,
Brian.
Ehm, I should probably have said that I am assuming the data is already in 30 sheets in the workbook. If any of this is incorrect, please let me know the details!
Regards,
Brian.
ASKER
Hi Redmonb.All 30 are in a spreadsheet. However, I should have said I am novice with excel. This work was given to me because I am the new guy. =)
Rhiaanon44,
We were all there once, but at least you've found your way here!
What we're going to do isn't going to be too complicated - as long as I explain properly, so please bear with me. :)
OK, some questions,...
(1) Which version of Excel are you using?
(2) If the file isn't too big, can you post it here? (If the data is confidential (or you're just not sure) then the correct answer is "No".) If not, can you tell me
Thanks,
Brian.
We were all there once, but at least you've found your way here!
What we're going to do isn't going to be too complicated - as long as I explain properly, so please bear with me. :)
OK, some questions,...
(1) Which version of Excel are you using?
(2) If the file isn't too big, can you post it here? (If the data is confidential (or you're just not sure) then the correct answer is "No".) If not, can you tell me
(a) what kind of file it is,
(b) on average, roughly how many rows per sheet,
(c) Are the headers for each sheet identical?
(3) Have you ever looked at a macro within Excel?(b) on average, roughly how many rows per sheet,
(c) Are the headers for each sheet identical?
Thanks,
Brian.
ASKER
There are 37 server names but I just put several bogus ones there. Also, there are 6 columns more of these kind of data. All 30 sheets have the same data requested for the 37 servers.
Excel.xlsx
Excel.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
John?! Sorry, Rhiaanon44, I mixed up two posts. Definitely time for bed!
ASKER
Thanks Redmonb. I will be back on in the AM. I am looking at your work. Awesome! Thanks!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you both! I cannot thank you enough!!!
Thanks, Rhiaanon44. Good suggestion Rob.
Please see the code below. A few notes...
(1) Set the XHEADER_ROWS constant to the no. of rows in the header (so it isn't duplicated from the second and subsequent sheets). You can set this to 0 if you want all rows from all sheets.
(2) The macro unprotects, unfilters, unhides rows and columns the two sheets.
(3) Because formulas may cause problems in the new "Merge" sheet, the macro pastes values only. Obviously it can be changed if you want formatting also or formulas instead.
(4) The macro doesn't bother checking if "Merge" already exists - instead it fails ungracefully.
(5) The results are slightly untidy if the second (or subsequent) sheet only has the header rows (i.e. no data).
Open in new window
Regards,Brian.