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"