Return to LaurenceHolbrook.com Technical Information Index

Creating and Using a Personal.xls Workbook

 

The best place to store macros that are general purpose in nature and to have them available all the time is to store them in your Personal Macro Workbook. This is a (normally) hidden workbook that is loaded automatically by Excel. When you record a macro, you have the option of recording it to your Personal Macro Workbook. The file, Personal.xls, is stored in your \XLStart directory. The Personal.xls file doesn't exist until you record a macro to it.

 

The start directory used by Excel in WIn2K is

C:\Documents and Settings\Laurence Holbrook.AT4107680452\Application Data\Microsoft\Excel\XLSTART

The following directory is also used

C:\Program Files\Microsoft Office\Office\XLStart

 

The easy way to create a personal.xls workbook is to record a macro to it -

 

                      Close any and all open Excel workbooks

                      Start Excel

                      Click menu item Tools|Macro

                      Click Record New Macro...

                      Click the down arrow In the "Store macro in:" list box

                      Click on Personal Macro Workbook

                      Click OK

 

Do something simple to create the macro...

 

                      Click in cell B1

                      Press the Delete key

                      Click in cell A1

 

                      Click on the square button in the Stop recording tool

 

                      Click the X in the top right hand corner of the Excel window to close it (or press <Alt><F4>)

                      Click Yes to respond to "Do you want to save the changes you made to the Personal Macro Workbook? If you click Yes, the macros will be available the next you start Microsoft Excel -

                      Click No to respond to "Do you want to save the changes you med to 'Book1'?

 

Start Excel

 

                      <Alt<F11> to open the Visual Basic Editor

 

                      Click the + next to the VBAProject (PERSONAL.XLS)

                      Click the + next to Modules

                      Click on Module1

                      Click on the View Code icon at the top of the Project explorer -

 

You can highlight all the code there (<Ctrl>a will select all) and press delete -

 

Let's add a couple of potentially useful general-purpose macros -

 

                      Highlight the following lines (you needn't include the starting and ending '-----------' lines)

 

'-------------------------------------------------------------

Sub CopyVisible()
'
' Macro recorded 7/1/2003 by Laurence Holbrook
'

Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy


End Sub
Sub PasteSpecialValues()
'
' Macro recorded 7/1/2003 by Laurence Holbrook
'

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
' Application.CutCopyMode = False

End Sub

'---------------------------------------------------------

 

                      Press <Ctrl>c

                      Press <Ctrl>v module1 of the VBAProject (PERSONAL.XLS)

                      Click menu entry Window|Unhide

                      Highlight Personal.XLS and Click OK

                      Press <Alt><F8>

                      Highlight CopyVisible and click Options

                      Type g in the Shortcut key

                      Click OK

                      Highlight PasteSpecial and click Options

                      Type f in the Shortcut key

                      Click OK

                      Click X to close the Macro select window

                      Click menu entry Window|hide

 

[Open any other workbooks you may have that contain 'general purpose' modules and copy the 'general purpose' code from those workbooks and paste into the Personal.xls Module1]

 

                      Close Excel

                      Click Yes to respond to "Do you want to save the changes you made to the Personal Macro Workbook? If you click Yes, the macros will be available the next you start Microsoft Excel -

                      Unless you made changes that you want to keep to any of the other workbooks, you can click No to any "Do you want to save the changes you med to 'xxxxx'? that appear

 

The next time you open Excel you will have added two more 'copy and paste' type functions - <Ctrl>g copies only visible cells - <Ctrl>f does a PasteSpecial Values - both work in conjunction with the normal <Ctrl>c, <Ctrl>v, <Ctrl>x functions -

 

You can use <Ctrl>c to copy some cells that have calculated values, select a destination and <Ctrl>f will copy the cell values to the destination instead of the formulas -

 

Compare <Ctrl>f to

 

                     Click menu entry Edit

                     Click Paste Special

                     Click the Values option button

                     Click OK

 

You can us <Ctrl>g to copy only visible cells - if you have used autofilter to 'hide' cells you are not interested in and display cells that you are, you can highlight the visible cells and <Ctrl>g will copy just those cells that you can see and will ignore the cells that are hidden - <Ctrl>v will paste just those cells to a new destination

 

Note: In the future, as you close workbooks you may get asked if you want to save changes to your Personal.xls workbook - unless you have made a change that you know you want to save, I would generally recommend clicking "No"