Excel Macro to Format all Worksheets in Multiple WB in a folder based on Given Criteria

Hi excelforum Experts,

I have these monthly reports exported from our client system in a form of excel however, there are colors and other irrelevant formats in each worksheets that I want to be removed and formatted. I have four to five worksheets per workbook (and about 32 workbooks in one folder).

I am wondering if there could be someone that could help me come up with an excel macro that will automate the process based on the given criteria. Normally, all workbooks are saved in one folder and its file path will be pasted on the macro template. I have attached two (2) workbooks here as references. The first one will be the tool that has button on it named 'Automate' and the other workbook is the sample raw data with the desired output on it.

The following criteria should run after I pasted the 'File Path' of the workbooks to be formatted and clicking the 'Automate' button:

1.) The macro will automatically go to the file path given and open each workbook.
2.) In each workbook, select all cells and remove all colors.
3.) The following cells should be converted into 'Comma Style' format: (B7:D37, F7:F14, B38:B47, L7:L36, F21, F24, F27, F30, F33, F36 and F39).
4.) The following cells should be converted into 'Number' format first and converted into 'Percent' format: (G7:K36, M7:M36 and C38:C47).

The criteria above should be applied to each worksheets in all workbooks.

Any inputs and ideas will be much appreciated. Thank you for helping me!


Warmest regards,
Arnel

Hi dflak,

Thank you for taking time in answering my post and I appreciate your comments inside the module which gave me additional ideas on how each code works.

I tried using the attached workbook and it really works except for the other worksheets (I have four to five worksheets per workbook). I appreciate if the macro could also loops across all worksheets in all workbooks and yes, they have the same layout.

Warmest regards,
Arnel