Excel - Multiple Worksheet Macros

A little while ago I was asked by one of our finance team to see if I could help reduce the workload required in maintaining a reporting Excel spreadsheet. Along with some very specific automation amendments to the spreadsheet I also created something quite generic to allow a macro to be run against multiple worksheets in an Excel workbook.

Originally finance were spending hours making amendments to 96 worksheets! This little helper allowed them to run the other amendments I made and process the whole workbook over their lunch hour (with time to spare).

Behind the scenes there’s a dialog box with a bit of VBA code that you call with the name of the macro you wish to run.   The spreadsheet includes a test function to show how to call the dialog box:

Select the macro to run
Public Sub MyTestMacro()
    LoopThroughWorkbooks ("MyMacro")
End Sub

Private Sub MyMacro()
    ActiveCell.FormulaR1C1 = "xx"
End Sub
Select worksheets

When the macro is called it will then display a dialog box with a list of the worksheets in the active workbook. Use CTRL and SHIFT to select the worksheets you want to run a macro against and then let it go.

Once you’ve selected the worksheets, just click the Run button and sit back.  Excel will then simply activate each of the selected worksheets in turn and run the macro you’ve specified.

If you do find this useful please include a link back to this blog.

Author: Stephen Millard
Tags: | excel |

Buy me a coffeeBuy me a coffee

Related posts that you may also like to read