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()
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.
Select worksheets
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.
The whole thing is available in a form (frmRunMacroOnManySheets) and a code module (modLoop) for you to download in the spreadsheet below. Simply copy these into a common workbook or the one in which you specifically need to run a macro against several worksheets.
- Download Spreadsheet (33.5 Kb)
If you do find this useful please include a link back to this blog.
Flag IT 