Retrieve key cols from multiple workbook and prepare report

[Guruprasad1987]

Hi I have folder that contains 100 excel workbooks - countries and systems . In each of these workbook I need to retrieve few columns and make a key . retrieve the chosen columns along with key for all the workbooks consolidate in master table . Once I run the macro I should be able to view the revenue across countries from various systems.

e.g my folder country 1 -system .xls
country 1 -system 2.xls
country 2 - system 1.xls
should be able to view country wise system wise revenue numbers .

 

[PaulF]

.xls << Can I ask what version of Excel you are using ??

I have a few projects like this... All use a VBA solution.

 

[Guruprasad1987]

Thanks Paul . Currently am using 2007 . Use .Xlsx also

 

[PaulF]

If you upload a couple of countryX sheets we should be able to help you.

 

[Guruprasad1987]

Thanks Paul . Have attached the sample of files country , system wise .I have also placed the expected result in the summary sheet . the macro must look up the files populate the respective data in the summary sheet as mentioned. It will be great if the code is not hardcoded . The number of rows for each month varies . Have also jumbled the columns as is the way we get the data . Seeking your kind help and support . Thanks .

For your needed info : The folder contains data for only two countries. The actual folder contains data from 50-60 workbooks . Need to consolidate data from all and prepare the above report


▬▬▬▬▬▬▬▬▬ Mod edit : thread closed as recreated in appropriate forum

​