Hi, I need help with a macro to copy ranges from one worksheet in the country file to multiple worksheets in the master file. The master file as well as the country files are stored in the same folder. I am attaching two workbooks 1. Consolidate Test and 2. Germany Test. The country file has a 'Highlights' tab where commentary is given against various sections e.g. Sales, Opex and HC. The Consolidated file has these sections as worksheets and the countries as sections on each tab. The idea is that countries feed in their commentary on the 'Highlights' tab but as a consolidator, I would like to see the highlights of all the countries for each section (i.e. Sales, Opex and HC). Since this is commentary, PasteValue should be absolutely fine.
I have an existing way where I am running a macro to copy all the 'Highlights' pages from each Country file into the consolidated file and then linking individual cells in each file to the various sections. For example, I would copy the 'Highlights' page from the 'Germany' file into the 'consolidate' file and then have a formula in Sales!B2 to pull the data from worksheet Germany!A2. While this works, it means adding about 24 more sheets into my workbook which I want to avoid.
So what I want is as follows:
1. Open the 'consolidate test' file and hit a macro.
2. the macro opens each country file in the folder and copies individual sections from the 'Highlights' worksheet into the different tabs on the consolidate file, closes the file and goes to the next one until there are no more files in the folder.
Appreciate any help in this regard. Am attaching a couple of files to make my point clear.
Rgds
Krish
Bookmarks