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)
'-------------------------------------------------------------
'---------------------------------------------------------
·
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"