Copy your macros to a Personal Macro Workbook

To create the personal workbook, first 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. For more information, see Show the Developer tab.

Create and update the Personal Macro workbook

1. On the Developer tab, in the Code group, select Record Macro.
   
2. In the Record Macro dialog box, type a meaningful name for the macro in the Macro name box. Don't use any spaces in the name.
3. In the Store macro in box, select Personal Macro Workbook.
   
4. Select OK.
5. Perform the actions that you want to record.
6. On the Developer tab, in the Code group, select Stop Recording.
   
7. Close the workbook.
   A message appears that prompts you to save the changes that you made to the Personal Macro Workbook.
8. Select Save to save the workbook.

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

Sharing macros

To copy macros between the personal workbook and another workbook, use the Visual Basic Editor (VBE). Press ALT+F11 to open the Visual Basic Editor in Excel. For more information about copying a macro from one workbook to another, see Copy a macro module to another workbook.

To share your Personal.xlsb file with others, copy it to the XLSTART folder on other computers. In Windows 10, save this workbook in the C:\Users\user name\AppData\Local\Microsoft\Excel\XLStart folder. Workbooks in the XLStart folder open automatically whenever Excel starts, and any code you store in the personal macro workbook appears in the Macro dialog.

If you have one or just a few macros that you want to share with others, send them the workbook that contains the macros in an email. You can also make the workbook available on a shared network drive, or from a SharePoint Services library.

To create the personal workbook, first 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. To do that:

On the Excel menu, click Preferences... > Ribbon & Toolbar. In the Customize the Ribbon category, in the Main Tabs list, select the Developer check box, and then click Save.

Create and update the Personal Macro workbook

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

1. On the Developer tab, click Record Macro.
2. In the Record Macro dialog box, type a meaningful name for the macro in the Macro name box. Don't use any spaces in the name.
3. In the Store macro in box, select Personal Macro Workbook.
4. Select OK.
5. Perform the actions that you want to record.
6. On the Developer tab, click Stop Recording.
7. Save the changes, close the workbook, and then close Excel.
   A message appears that prompts you to save the changes that you made to the Personal Macro Workbook.
8. Select Save to save the workbook.

Any time you create a new macro and save it in your personal workbook, or update any macros already in it, you're prompted to save the personal workbook.

Sharing macros

To copy macros between your personal workbook and another workbook, use the Visual Basic Editor (VBE). In Excel, select Visual Basic in the Developer tab to open the Visual Basic Editor. For more information about copying a macro from one workbook to another, see Copy a macro module to another workbook.

To share your Personal.xlsb file with others, copy it to the Excel home folder on other computers. For Mac, the folder is in the home folder at ~/Library/Containers/com.microsoft.Excel/Data/Library/Application Support/Microsoft/Roaming/Excel/.

If you have one or just a few macros that you want to share with others, send them the workbook that contains the macros in an email. You can also make the workbook available on a shared network drive, or from a SharePoint Services library.