Excel macro that pulls data based on header name from multiple sheets

[Anand307]

Hi Team,

I have 20 multiple excel workbook downloaded and saved daily in a drive. I need to open all these workbook individually and copy the data from two columns with the header name asDonorandAmount.These two columns vary with their address on all 20 workbooks. Hence I need a macro that can find the column names asDonorandAmount. and copy the entire columns data and paste in new excel workbook and do untill the data is copied and pasted from all 20 workbook.

Additional information - 20 excel workbook will be named as 1,2,3 etc serially. Daily the count of total workbook varies, sometime it might be total of 15 sometimes 20.

 

[Luke M]

Hi Anand307,

Are the workbooks that you want to copy all in the same folder? If yes, are there any other files in the folder that we wouldn't want to copy?
Do you want all the data merged below each other, or across?

 

[Anand307]

Hi Luke,

1.Yes all the workbooks are saved in a same folder.
2.No, there are no other files in the folder.
3.data needs to be merged below each other.

Let me know if you would require more information.

Regards,
Anand

 

[Luke M]

Try this to start. Click the button, then choose which folder has the files. Macro will create a new workbook to merge all the information into.

 

[Anand307]

Hi Luke,


Great! Thanks for the help it is working successfully.


Thanks & Regards,

 

[Anand307]

Also wish you Happy Birthday Luke