personal macros1

If you find yourself using the same macros over and over again, you can create a personal *.xlsb file that opens every time you start excel. You need excel 2010 for this tutorial.

Create a personal *.xlsb file

  1. Create a new workbook (CTRL + n)
  2. Go to VB Editor (Alt + F11)
  3. Insert a module
  4. Copy macros to module
  5. Exit VB Editor and return to excel
  6. Save workbook as a *.xlsb file to this folder:
    C:\Users\<username>\AppData\Roaming\Microsoft\Excel\XLSTART
  7. Exit excel
  8. Start excel
  9. Your personal.xlsb file opens automatically
  10. Go to tab "View" on the ribbon
  11. Click "Hide" button
    personal macro file - hide workbook
  12. Exit excel

The next time you start excel, a new workbook is created. The personal.xlsb opens but is hidden.

Link macro to excel 2010 ribbon

Excel 2010 allows you to customize the ribbon and link your personal macros to a button each. So you can quickly and easily access your favorite macros.

  1. Go to "File" on the ribbon
  2. Click "Options"
  3. Click "Customize Ribbon"
  4. Click "New tab"
    excel 2010 - customize ribbon
  5. Rename new tab
  6. Select macros from drop down list
  7. Select a macro
  8. Click "Add"
  9. Click "Rename"
  10. Pick an icon and type a name
  11. Click OK

link personal macros to ribbon

Example macros to save in your personal.xlsb file

Read more

Copy your macros to a Personal Macro Workbook

Excel 2010 Customizable Ribbon