JCJG
asked on
Excel Macro to consolidate data from different worksheet
Hi, I'd like to consolidate data from different worksheets into a single worksheet. Please see the attached file.
The goal is to consolidate any number of worksheets placed between the "S" and "E" tabs in the following way:
1 Copy column headings from the first sheet "Blue" to the "Consolidation" tab starting cell N1 and across
2 Copy data from the first tab after "S" tab to the "Consolidation" tab starting N2
3 Add the name of the worksheet (e.g. Blue) to column M of the data
4 Repeat 2 to 3 for the next sheet until next sheet is "E"
Please refer to the "Example" tab for the end result. Thanks!
Consolidate.xlsx
The goal is to consolidate any number of worksheets placed between the "S" and "E" tabs in the following way:
1 Copy column headings from the first sheet "Blue" to the "Consolidation" tab starting cell N1 and across
2 Copy data from the first tab after "S" tab to the "Consolidation" tab starting N2
3 Add the name of the worksheet (e.g. Blue) to column M of the data
4 Repeat 2 to 3 for the next sheet until next sheet is "E"
Please refer to the "Example" tab for the end result. Thanks!
Consolidate.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, sdwalker! This works well! Is it possible to add the following?
(1) flexibility to add or delete columns in the result sheet (the blue area). In other words, not hard code the columns to paste data. In this case, perhaps use the column heading "Name" as the location to perform clear cell content and paste data?
(2) The data tabs between "S" and "E" have additional rows that I don't need. They contain the word "total" in the "Start Date" column. Can we delete them after copy and paste to the result tab?
(3) Can the formulas in the blue area be automatically copied down to last row?
(1) flexibility to add or delete columns in the result sheet (the blue area). In other words, not hard code the columns to paste data. In this case, perhaps use the column heading "Name" as the location to perform clear cell content and paste data?
(2) The data tabs between "S" and "E" have additional rows that I don't need. They contain the word "total" in the "Start Date" column. Can we delete them after copy and paste to the result tab?
(3) Can the formulas in the blue area be automatically copied down to last row?
Those are all very doable, but it'll take a decent amount of add'l work to complete. Can you close this question (and award points) and open a new question with the new requirements? I've just not had a chance to look at this again.
If you can't open a new questions, let me know and I'll try to get back to this in a day or so.
Thanks,
sdwalker
If you can't open a new questions, let me know and I'll try to get back to this in a day or so.
Thanks,
sdwalker
I've contacted Experts Exchange and, since I've answered the original question, the next step is for you to close this question and open another one with your new requirements and post a link back to this question.
Thanks,
sdwalker
Thanks,
sdwalker
JCJG, no comment at all? Are you not going to award points for the answer?
They are:
Data > Consolidate
Doesn't seem to do anything
Pivot Table with multiple sources.
Creates summary but adds up or counts the value for the months rather than listing them.
For both of these I have added a column to the data tabs with the following formula:
=MID(CELL("filename",A$1),
This extracts the sheet name so can be included against each record.
I have worked on similar but the other way round which may be worth considering. A single list with all data records and then a VBA routine to split it out into separate sheets.
Thanks
Rob H