Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I want all of my macros that I've written to always be available to me when I'm using MicroSoft Excel 2010. How can I do this?

share|improve this question
add comment

2 Answers 2

up vote 4 down vote accepted

In excel 2010, the personal macro workbook is located at C:\Users\UserName\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.xlsb. The best way to create this is to record a macro into your personal macro workbook. You do this by:

  1. Enabling the developer tab by going to File - Options - Customize Ribbon and checking developer on the right side of the screen
    enter image description here
  2. Go to the developer tab and click on record macro
    enter image description here
  3. Store Macro In your Personal Macro Workbook
    enter image description here
  4. Stop Recording macro
    enter image description here
  5. When you close out of excel or the workbook, it will prompt you Do you want to save the changes you made to PERSONAL.xlsb select save
    enter image description here

    You will now have a personal macro workbook that is visible anytime you open excel. If you have macros stored in other workbooks you can transfer them to the personal macro workbook by moving the modules within Visual Basic in excel. Additionally, if you have a workbook with a lot of macros, you can navigate to the file location and rename that workbook to PERSONAL and save it as .xlsb

share|improve this answer
add comment

I know you've answered your own question here, and it is helpful, but for people wanting to transfer an old personal macro workbook from an Excel 2007 and below, to Excel 2010, they should copy

%APPDATA%\Microsoft\Excel\XLSTART\PERSONAL.XLS

to the same location on the new machine, without renaming it XLSB. Depending on the version of windows, %APPDATA% will point to different locations, so they should use the windows variable when typing their pathname on both machines.

As you said, the directory XLSTART won't exist until a personal macro workbook is created, but manually creating it won't hurt, so long as they restart Excel.

share|improve this answer
    
This is true, the .xlsb file-type didn't exist until 2007. –  Raystafarian Mar 26 at 14:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.