Record Macros in a Personal Macro Workbook

When recording macros in Excel you have the option to either save the macro in the current workbook, a new workbook, or the 'Personal Macro Workbook'. The Personal Macro Workbook is stored at this location:

C:\Users\Your name\AppData\Roaming\Microsoft\Excel\XLSTART . It can be used as a 'storehouse' for macros that can accessed from any workbook.

On the Developer tab when you click on 'Record Macros' you get the option to choose the macro's name and then select where you want to save it.

You then perform a series of actions and click on Stop Recording on the Developer tab. Before you can begin recording if you have selected 'Personal Macro Workbook' you may get this error message:

In order to get around this and successfully save your new macro go to File . . . Options . . Addins. In the Manage menu at the bottom select 'Disable Items' and click 'Go'. You should see the option to enable the disabled Personal Macro Workbook.

Note that you are warned it may be necessary to restart Excel in order for the changes to take effect. After doing so, you should then be able to successfully record a macro in the Personal Macro Workbook. This workbook file will be displayed in Project Explorer in Visual Basic.