How to make MS Excel macros to run across all sheets


MS Excel macros can boost your productivity, saving you a lot of the time you spend on doing boring tasks, such as formatting or printing. If you didn’t know by now, find out how you can use a macro across all worksheets:

personal sheet macro

After you choose Tools, Macros, Record New Macro, in the dialog box, at “Store macro in:“, go and switch from the default This workbook, to Personal Macro Workbook.

This will allow you to use the macro in whatever worksheet (or workbook) you need, without having to open the file you recorded it in every time. If you define a shortcut key for it (see picture), you’ll access it very quickly. If you feel like doing so, you can even assign a toolbar button to your macro. In case you want to launch the macro only once for all the sheets in a workbook, just select all worksheets (press Shift and click on each sheet name tab), then launch the macro. It will run in all selected worksheets.

Attention! Don’t forget to save the Personal Macro Workbook when prompted, otherwise you’ll lose your macro.

5 Comments

  1. Anyone can please advise how to create a single macro to run all the macros in different worksheets at once. When i click on the button of a macro, the macro1 should run and then Macro2 in another workseet and then Macro3 in a different worksheet.

  2. Hey Alvin,

    Define a new macro and store it in the Personal Macro Workbook. This new macro should consist of all other macros that you need to run in separate sheets. I never tried this, but I think it should work.

  3. I recorded a macro that changes the footer for a particular sheet. Then, I selected multiple sheets and ran it. I found that the macro only applies to the active sheet, not all selected sheets. Any suggestions?

Comments are closed.