Save WorkSheets to Multiple Folders based on the WorkSheet Name

I have a Workbook containing a Summary Tab, a Hidden Worksheet and any number of worksheets named X00120, X00121, X00125 and so on (see attached). These worksheets all include cells with formulas.

I then have a folder containing subfolders that use the names of the worksheets. There will be more folders than there are worksheets, so my folder structure looks like this:

Exports.jpg

For each Worksheet named X00123, X00124, X00125 and so on, I need to do the following with a single macro:

1. Copy and Paste Values to remove the formulas on these sheets only.
2. Save each Worksheet (excluding the Summary sheet and the Hidden sheet) as a both a .xlsx and a .pdf file to its respective folder, so X00123 will be automatically saved to the folder X00123, X00124 will be saved to the folder X00124 and so on.
3. Prefix each filename with 'Summary Expenditure Report', so the files saved in each folder should be named 'Summary Expenditure Report - X00123', 'Summary Expenditure Report - X00124' and so on for both the .xlsx and the .pdf versions

I need the option to run the macro from a button on the summary sheet so it will save all the files in one go automatically without asking the user (although the user should be asked if they want to overwrite the file if it already exists) and also the option to have a button on each worksheet that achieves the same thing but only for the active sheet.

Is this possible? and it is possible to achieve in a single macro or would this require two different macro's, one to save all sheets and a second to save individual sheets?

Many thanks

Hi Lewis,

OMG, thanks for this, it must have taken you hours! I now need to get my head around each element of the code and understand it to see which elements are doing what...

Can I check it out and come back to you with any questions, which I'm sure I'm going to have...

Many thanks