VBA :: Consolidate ranges in one worksheet and copying the MasterData in another workbook
Posted on 2011-10-07
Within one repository workbook called MasterData, I have many worksheets with monthly data and one worksheet called MasterData with all the consolidated monthly data.
I have also a second workbook called MasterPivot where a copy of MasterData, the worksheet with consolidated monthly data is transformed.
In MasterData workbook:
- Each worksheet have the same headers but vary widely in the number of rows from 65K to 100K.
- Each individual worksheet data (without its headers) is named as a range with an unique name (i.e.: Aug, Sept, etc).
Objective 1: each time a new named range (worksheet of monthly data) is added in a separate worksheet within the MasterData workbook, I want to consolidate it in the MasterData worksheet.
All the MasterData worksheet (including its headers) should be define as a range named: MasterData.
Question 1.1: what is the macro required to consolidate together all ranges in the MasterData worksheet?
Question 1.2: can the MasterData range itself be updated after the consolidation process?
In MasterPivot workbook:
Currently, I manually copy this MasterData worksheet as is into a workbook called MasterPivot.
There, the MasterData worksheet (with its headers) is again defined as a range named MasterData.
I use the MasterData range to populate formulas, pivot tables and charts within some worksheets of the MasterPivot workbook.
Objective 2: Copy the MasterData range to the MasterPivot workbook one and only once after a consolidation process had been done in MasterData worksheet. Define a range called MasterData from that copied content in MasterPivot workbook.
Question 2: is there a macro that can copy the MasterData range from MasterData workbook to the MasterPivot workbook, after a consolidation process had been performed in the MasterData workbook?
Requirements: this setup is to maintain the following:
- MasterData as a repository of all the raw monthly reports acting as a "paper trail" for future references.
- MasterPivot as a "transformation layer" to apply calculated metrics, to organize data in tables and graphs.
Thanks for the time you took to read this long email.
Hope it is clear enough.