Extract data from multiple workbooks

[mclav]

Hi Folks,

Need your expert help and advice for the following problem. I apologize for being too descriptive about the problem

I have 15 workbooks and a Master workbook with multiple worksheets.

I need to extract the data from these 15 workbooks and paste them to a specific sheet named 'Summary' on the master workbook.

Problem is, all the 15 workbooks have headers which are inconsistent. They do not match the header of the 'Summary' worksheet in the Master workbook.
I usually create a copy of the original workbooks (so that i have a backup of the all 15 orignal workbooks) and manually delete the columns which are not required and arrange the headers and the corresponding data to match the header of the 'Summary' worksheet in the Master workbook and then paste the data to the 'Summary' worksheet.

Is there a way to automate the following tasks using VBA
1) Make a copy of each of the 15 workbooks.
2) Deleting the unwanted columns from 15 workbooks by comparing it with the header in the Summary worksheet of the Master workbook.
3) After Step2 , paste the required data (from the 15 workbooks) into the Summary worksheet of the Master Workbook.

Thanks again, Appreciate your help!
Archer

[arlu1201]

Yes this can be done.

Please attach a sample of the original workbook and of the master workbook.

[mclav]

Sure, i have attached 3 excel workbook with dummy data and a Master xls with Summary worksheet. The Summary worksheet has the required Column Header format.

Thanks again!

Archer

[jaslake]

Hi mclav

This code is in the attached. It appears to do this

paste the required data (from the 15 workbooks) into the Summary worksheet of the Master Workbook

without having to do this

1) Make a copy of each of the 15 workbooks.
2) Deleting the unwanted columns from 15 workbooks by comparing it with the header in the Summary worksheet of the Master workbook

See if it works for you.

Please Login or Register  to view this content.

[mclav]

Thanks a lot and apologize for responding late. The code works for me

Appreciate your help!!!

[jaslake]

Hi Archer
You're welcome...glad I could be of help.