Excel/Different sheet names everytime, so unable to consolidate the data
QUESTION: Hi Jerry,
Could you please suggest me a macro that pulls particular data from different sheets and paste it in a single sheet. I have macro for consolidation but there is a problem. We will get report from market every week. So we need to consolidate the data manually. The sheet name and sheet count will differ (Eg: this week we may get 10 sheets with some name and next week 13 sheets with some other names) all the time.
So I could not able to prepare a macro to pull the data. Kindly suggest me a macro to resolve this issue. Thank you.
ANSWER: So, you are looking to consolidate data from multiple sheets.
Sometimes people use the wrong terms, so I want to clarify waht you're really asking.
Workbook - an Excel workbook, inside can be any number of sheet
Worksheet - a single sheet within a workbook, any number of sheets can be present
1) Multiple workbooks with a single sheet in each, each workbook needs to be opened from a specific folder and all data on the one sheet imported into a single consolidation sheet
2) Multiple workbooks with a MULTIPLE sheets in each, each workbook needs to be opened from a specific folder and all data on the ALL sheets imported into a single consolidation sheet
3) Multiple workbooks with a MULTIPLE sheets in each, each workbook needs to be opened from a specific folder and all data on SOME OF THE SHEETS needs to be imported into a single consolidation sheet, we will need to decide how to determine which sheets to import and which to ignore within each workbook
4) A single workbook is open onscreen already, within which there are MULTIPLE sheets, consolidate all data from all sheets into a single sheet
Does any of those cover your need?
---------- FOLLOW-UP ----------
QUESTION: Hi Jerry,
Could you please suggest me a consolidation macro. We will receive the weekly report (only one excel sheet) with 50-60 pages. In each Sheet, contents remains same as shown in the image. But the problem is, each week, the sheet name will change and sheet count will also change (for example if this week, I have received report with 56 sheets, next week it may differ as 53 sheets or 58 sheets etc). So I could not able to make a consolidation macro.
Kindly suggest me a macro to consolidate the content from different sheets to one main sheet (in another excel). Thank you in advance.
'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.