Rename header rows based on excel workbook names

[LauraE]

Hello
I have hundreds of separate excel workbooks saved in a single folder. Each workbook has the same set of 28 variables with the same header row titles and contains monthly data on the cost of drugs. I want to run a loop to merge these workbooks into one. There are two main issues (1) the first column contains the drug code which I want to match each workbook on in the master excel workbook (and additional drug codes can be added each month), and (2) the headers are all the same but I want to rename these by the excel workbook name before merging into the master excel workbook so that I have the the header rows as variable_a_jan_2018, variable_a_feb_2018, etc. Is it possible to run an excel macro loop to achieve this?
Any help would be greatly appreciated

 

[dmt32]

Hi,
welcome to forum

have a look here:https://docs.microsoft.com/en-us/pr...e/developer/office-2007/cc837974(v=office.12)

and see if Ron's article goes someway in meeting your requirement.

Dave