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
- Create a new workbook (CTRL + n)
- Go to VB Editor (Alt + F11)
- Insert a module
- Copy macros to module
- Exit VB Editor and return to excel
- Save workbook as a *.xlsb file to this folder:
- Exit excel
- Start excel
- Your personal.xlsb file opens automatically
- Go to tab "View" on the ribbon
- Click "Hide" button
- 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.
- Go to "File" on the ribbon
- Click "Options"
- Click "Customize Ribbon"
- Click "New tab"
- Rename new tab
- Select macros from drop down list
- Select a macro
- Click "Add"
- Click "Rename"
- Pick an icon and type a name
- Click OK
Example macros to save in your personal.xlsb file
- Remove print preview lines (Page Breaks)
- Create links to all sheets in a workbook
- Insert hyperlinks to all files in current folder
- Follow hyperlinks in a pivot table
- Select cell A1 on all sheets before you close a workbook
- List files in a folder and subfolders