How to make a macro available in all excel sheets

Amanda receives monthly sales figures in excel from all the company sales reps .

She had to manually summarise the data for each report and generate  charts for the director, which took her over 2 days.

She wrote a macro to automate the process. Now it takes her a few seconds per file.

However, she has to still copy the macro to all the excel files to execute it which takes over an hour for all the reports.

Next month, when she receives the new files, she has to again copy the macros to all the files and repeat the whole process.

She reduced the processing time for all the sheets to just a few minutes by moving the macro to a personal workbook.

Here is how to do that!

If macros are stored in a personal workbook, they are available to be used in all the excel sheets on the computer

A personal workbook (Personal.xlsb) is opened as a hidden workbook every time you start Excel.

To create the personal workbook, you first need to create a macro and store it in the Personal Macro Workbook.

Before you get started, make sure that the Developer tab is available in the ribbon in Excel.

We will create a simple macro using the Macro Recorder. This macro will change the color of the text in the current cell to red.

 

  1. In cell A1, enter some text, such as “Black text”
  2. On the Developer tab, in the Code group, click Record Macro.
  3. In the Record Macro dialog box, type a name for the macro in the Macro name box, such as ColorMe. Make sure you don’t use any spaces in the name.
  4. Under the shortcut type r, so you can run the macro using Ctrl+r key combination
  5. In the Store macro in box, select Personal Macro Workbook.

 

  1. Click OK.
  2. Go to the Home tab and set the text color to Red.

 

  1. That is only step that is recorded in the macro.
  2. On the Developer tab, in the Code group, click Stop Recording.
  3. Close any open workbooks and then exit Excel.
    A message appears that prompts you to save the changes that you made to the Personal Macro Workbook.
  4. Click Yes to save the personal workbook.

 

To execute the macro on a new sheet follow these steps:

  1. Open excel again and type something like “New Text” in cell B2.
  2. Press Ctrl+r and you will see the color of the text in the cell turn red.
  3. If you haven’t create a shortcut, you can execute the macro manually
  4. On the Developer tab, in the Code group, click  Macros to open the Macro window
  5. Select Personal.xlsb from the “Macro In” dropdown and select ColorMe from the MacroName listbox
  6. Click on Run to execute the macro

 

When you open excel, the personal workbook is loaded but you can’t see it because it’s hidden by default. You can view Personal.xlsb by doing the following:

  1. On the View tab, in the Window group, click Unhide.
     
    In the Unhide dialog box, you should see PERSONAL.XLSB.
  2. Click OK to view the personal workbook.
    Any macros you save to the personal workbook can be edited only by first unhiding the personal workbook. This prevents you from accidentally deleting or making unwanted changes to those macros.
  3. To hide the personal workbook, make sure you have Personal.xlsb selected, and then click Hide.

Any time you create a new macro and save it in your personal workbook or update any macros that it contains, you are prompted to save the personal workbook just as it did the first time you saved it.

Leave a Reply

Your email address will not be published. Required fields are marked *