Excel - Multiple Worksheet Macros01 Feb 2009
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:
Public Sub MyTestMacro() LoopThroughWorkbooks ("MyMacro") End Sub Private Sub MyMacro() Range("B2").Select ActiveCell.FormulaR1C1 = "xx" End Sub
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.