You are here:

Excel/Different sheet names everytime, so unable to consolidate the data

Advertisement


Question
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


Is it:

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 ----------

Consolidation Macro
Consolidation Macro  
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.

With Regards,
Jay.

Answer
'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.

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jerry Beaucaire

Expertise

Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files

Experience

Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files ===================== I have been offering free assistance as an Excel aid on many web sites for many years: (http://www.excelforum.com - JBeaucaire) ======== (http://www.askmehelpdesk.com/spreadsheets - JBeaucaire) ======= (http://www.mrexcel.com/forum - jbeaucaire)

Education/Credentials
Bachelor's Degree from Azusa Pacific University in Mathematics and Music Composition

Awards and Honors
Microsoft Excel MVP 2010

©2016 About.com. All rights reserved.