Making macros available to other excel files

[Fred Zack]

I have a macro I copied from a website to one particular file. Is there a way
to make it available to other excel files without having to copy it to every
file I want it in? Seems I did this before but can't recall how. I don't
really care if its available to every excel file I have since I am the only
person using this computer and I trust this macro.

 

[Gord Dibben]

Store the macro in your Personal.xls workbook to have it available for all open
workbooks.

If you don't have a Personal.xls yet, with your workbook open, go to
Tools>Macro>Record a new Macro>Store Macro in;>Personal Macro Workbook.

Record yourself copying and pasting something then Stop Recording.

Alt + F11 to open the VBEditor.

You will see the two workbooks. Yours and Personal.xls

Copy the macros/functions from your workbook into the module1 in Personal.xls.

Note: any hard-coded sheets or books should be changed to ActiveSheet or
ActiveWorkbook

Delete the bogus copy/paste macro if you choose.

Personal.xls can be hidden and saved so's it always opens in the background with
macros available.

Clear the maros/functions from the original workbook if the above works OK.

An alternative to Personal.xls is to open a new workbook, copy the macro(s) to a
module in that workbook.

Save As an Add-in which you load through Tools>Add-ins.


Gord Dibben MS Excel MVP

 

[Otto Moehrbach]

Every macro in any open workbook is available to any and all other open
workbooks. It is customary, to do what you want to do, to place that macro
in your Personal.xls workbook. That workbook is opened automatically
whenever Excel is opened, and is usually a hidden workbook. HTH Otto