Nov 29 2010

How to Use Your Personal Macro Workbook

For any of the repeating tasks that you do through macros, Excel provides a way to have those commonly used actions always available to you. Instead of copying the macros you use frequently to every workbook, Excel gives you use of a Personal Macro Workbook.


Quickly Create Your Personal Workbook
Follow these steps to create your own personal.xls file:

  1. Create a new personal.xls file in the XLStart folder.

    Navigate to C:\Program Files\Microsoft Office\Office 10\XLStart (default install location). The quickest way to create the new file is to right click in the folder and click New–>Text Document. Then just rename that file to Personal.xls.

  2. Select from the menu bar: Tools–>Macros–>Record New Macro
  3. The box below will appear and you will now be able to record a macro to your personal workbook. Select “Personal Macro Workbook”.
  4. PersonalWorkbook

Hiding and Unhiding the Personal Workbook
Hiding your personal workbook is as easy as selecting Window from the menu bar, clicking Hide and choosing the workbook you would like to hide. Unhiding is much the same way. Just select Window–>Unhide and then select the workbook you would like to unhide.

Using the Personal Workbook
You can use the personal workbook in multiple ways. You can use any of the macros saved to this workbook any time you open a spreadsheet. Just select Tools–>Macro–>Macros and choose the macro to run. All macros in your personal workbook will be preceded by “PERSONAL.XLS!”.

You can also assign shortcuts of your choice to the macros that you keep in your personal workbook (ie. ALT+SHIFT+L) to quickly run your macros without going to the menu bar. Adding shortcuts can be done by selecting the macro you want to run and choosing edit from the options at the right. You can then assign your shortcut in the settings for that macro.

Personal Workbook in VBA Editor

Personal Workbook in VBA Editor

More Excel Tips

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 

          
TAGS: ,

12 Comments on this post

Trackbacks

  1. All VBAs are now updated | RANDOM WALKS – One Man's Markov Chain wrote:

    [...] as a custom toolbar. If you want to use these macros regularly, you may save all my codes to your personal macro workbook, and change the links of the buttons to refer to those codes. Please note that the toolbar exists [...]

    December 12th, 2011 at 12:00 am
  2. All VBAs updated | RANDOM WALKS – One Man's Markov Chain wrote:

    [...] as a custom toolbar. If you want to use these macros regularly, you may save all my codes to your personal macro workbook, and change the links of the buttons to refer to those codes. Please note that the toolbar exists [...]

    October 23rd, 2013 at 8:42 pm
  1. Lloyd Wood Devix said:

    Great article, very useful information.

    Lloyd Wood Devix Co RO

    November 29th, 2010 at 10:52 am
  2. Memorial Video said:

    I always not comfortable with Macros. But you described here superbly step by step. I’m happy to learn it. Excel is more in process in our company as it handling all the financial stuff. thanks for all..

    December 2nd, 2010 at 1:21 pm
  3. Divya said:

    Hi,

    Nice article …It has very useful to us. First I don’t know how to create the personnel workbook and hide it by reading this i understood.I can build my Excel addin in Excel 2010.
    http://godwinsblog.cdtech.in/2010/12/microsoft-office-excel-add-in-plug-in.html

    December 9th, 2010 at 3:14 am
  4. hedging said:

    Ok I’ve done that and a new personal workbook was created. However it does not open automatically when I open Excel. Book 1 opens when starting. Maybe thats not a problem. But……….I have several macros on my old computer in the Personal file. How can I get these transferred?

    December 15th, 2010 at 11:47 am
  5. john said:

    You can just copy and paste from the old workbook into the vba module of the new workbook. After that you can set any shortcut keys you want on those macros in the personal workbook.

    Also, the personal workbook might be hidden (which is how I like to be) when you first open a new workbook.

    December 15th, 2010 at 1:14 pm
  6. Gurgaon property said:

    macro is best option to make work complete easily with less efforts.
    I like it most for my excel work record sheet……….

    January 4th, 2011 at 6:35 am
  7. hypnosis said:

    to delete and edit any macro in MS Excel, the personal workbook must first be unhidden, but as iam new to Office 2007 , I am unable to find the path.

    April 25th, 2011 at 6:20 am
  8. Norman Taylor said:

    A very useful article. However, I have this problem in using the Personal Macro Workbook in Excel 2007. A macro runs perfectly in the Workbook for which it was written. The same macro copied and pasted into the PMW gets the “Runtime error ’9′, Subscript out of range” message. The macro switches between Sheets 1 and 2, but the PMW has only Sheet 1. Is this the problem, or is there something else? I should really appreciate any comment.

    May 15th, 2011 at 10:18 pm
  9. Leaflets said:

    to delete and edit any macro in MS Excel, the personal workbook must first be unhidden, but as iam new to Office 2007 , I am unable to find the path.

    January 19th, 2012 at 7:22 am
  10. Leaflets said:

    your question is not clear. Hit question mark (Help) on right corner of the excel ribbon. you will find several answers. if you have hidden the file itself,go to search and enter the file name and then hit on the properties and uncheck the hidden attribute.

    January 19th, 2012 at 7:23 am

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

  • Expert Excel Consultants for Business
  • Advertise Here

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories