Moving Macros from Personal.xls to "This Workbook"

[TinaF]

I created macros and stored them in Personal.xls and now want to move
them to "This Workbook". How do I do that?

 

[RichardSchollar]

Tina

What are these macros for? The ThisWorkbook module exists in every
Workbook (even the Personal.xls), and is there to trap events (such as
the workbook being opened) so that specific code can be run. Do you
have a need to do this? If you go into the Visual Basic Editor
(Alt+F11) you should see the code modules in your Personal.xls. You
can simply select the text and copy and paste into a code module in
another workbook (the macros are stored as text data), so it isn't
difficult to do, I would just be certain first that you really want it
in a ThisWorkbook module. Perhaps you could explain your reasoning?

Best regards

Richard

 

[TinaF]

Richard,

Excel is loaded on my C drive and I created a workbook and stored it
out on our network drive. When I went to someone else's desk and
opened the workbook on their computer and selected "enable macros" the
macros didn't function and I believe I got a message about Excel not
being able to find personal.xls. Since other people in the company
will be using this workbook off of the network drive, I need to store
the macros where they will work for everyone.

Tina

 

[Peo Sjoblom]

If these macros need to be in a particular workbook you should not put
them in ThisWorkbook, you put event macros there like for instance if
you need a macro to run automatically when the workbook is opened.
Put them in a regular module in that particular workbook, open the
workbook, press Alt + F11, in the VBAPproject pane to the left select
the workbook in question, click insert>module and in the large blank
window that opens paste the code from the personal.xls, close the VBE by
pressing Alt + Q. Save the workbook. Another way would be to export the
module from Personal.xls as a *.bas file and import it to the workbook

Regards,

Peo Sjoblom

 

[RichardSchollar]

Tina

Can you post the code behind the macros you are trying to run? Assuming
they are self-contained (ie don't refer to other workbooks - like
Personal.xls) the fact that Personal.xls couldn't be accessed shouldn't
matter. Can you see the code for the macros actually in the workbook
on the network? Please post this.

 

[Nick Hodge]

Tina

I think some other responders are confused by the ThisWorkbook terminology
where in fact you typed This Workbook. (Space). I suspect therefore that you
are talking about the option in the record macro dialog for personal.xls,
This Workbook, New workbook. You can open the VBE (Alt+F11) on your machine
and in the project explorer you will see your entry for personal.xls, below
this, (You may need to expand the tree), you will see a module(s), right
click on each one and select Export... (Save these files where you like)

Now open your workbook and open the VBE. You will now see an entry in the
project explorer with your workbook name. Right-click on this project name
and select import... navigate to your file(s) and import. This transfers
them to the workbook in question.

This makes no guarantee the modules will function outside of their native
environment. They should, but best work with a back-up

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk