h1

Excel – Multiple Worksheet Macros

01/02/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:

Select the macro to run

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

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.

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: