Macros in Book1 instead of Personal.xls

[woody3737]

Long story short, I recently updated my home computer to Excel 2007. I am in the process of setting everything up the same way my office Excel is, most importantly my macros. I have everything transferred over and working fine, but mistakenly put them all in a Book1 instead of the Personal.xls, so that they are available in any workbook I have open. So now, I have to have that Book1 open in order to use any of them.

One problem I am having correcting this problem is that I can't seem to find the Personal.xls. The unhide function is greyed out so I know it isn't hidden. So I guess the problem is two-fold. How do I create/restore the Personal file and then how do I transfer my macros to that so they are accessible in any work book? Thanks for any help.

 

[rguy84]

This may help: http://www.pcuser.com.au/pcuser/hs2.nsf/lookup+1/2C4A95765926E60CCA256C7600278AAA

 

[shg]

The easiest way to create Personal.xls is to record a macro, and when prompted, select putting it in Personal.

After you do that, you can drag modules from Book1 to Personal in the VBE.

 

[woody3737]

Thanks for the help. I have created the Personal file but am having trouble moving the macros over. I have very little VBE experience and can't figure out how to move them from Book1 to Personal. I recreated all the macros in the Personal file, but whenever I go to use any of them, it still opens up the original Book1, even though I thought I deleted it. Any ideas?

 

[shg]

Assuming you've moved all the mcros to Personal, then there are two ways.

Here's the first:

1. In the VBE Project Explorer window, select the VBA Project in Personal.

2. Do Tools > VBAProject Properties

3. Change the project name to something recognizable (mine is projPersonal)

4. Select the VBA Project in your workbook

5. Do Tools > References, look for projPersonal, and tick the box.

Here's the alternative:

Change every formula that uses your UDFs from =myFunction(args) to =Personal.xls!myFunction(args)