MACRO recording to be for all new sheets

[Christine]

I frequently download data from our stock system.

Our stock system will auotmatically create a excel file called 'Sheet1'

I need to do standard inserting of lines, adding of text and formating.

So I recorded a macro to do all these.

How can I make this macro available to all workbooks or sheets
( I do more than 10 downloads a day.)

 

[Alan Moseley]

I would store the macros in a separate worksheet, save it as an add-in, and
add it to your list of add-ins.

 

[Centrol]

Can you explain step by step how to:

- save in a separate worksheet (what file and which directory?)
-Save it as an add-in (how?)
- add it to your list of add-ins (how?)

thks

 

[Alan Moseley]

The following web page details this process:-

http://msdn.microsoft.com/en-us/library/aa140936(office.10).aspx

 

[Gord Dibben]

See in-line

Can you explain step by step how to:

- save in a separate worksheet (what file and which directory?)

Turn on the macro recorder from Tools>Macro>Record new macro.

Select Personal Macro Workbook as the place the store the macro.

Record yourself doing something like copy a cell from here to there then
Stop Recording.

You now have a Personal.xls stored in your XLSTART folder.

This file will open each time you start Excel.

To add existing macros to it, just copy them from your existing workbook
module to Personal.xls module by opening the Visual Basic Editor(Alt + F11)

Alt + q to return to Excel window. Window>Hide Personal.xls

Close Excel and save changes to Personal.xls when asked.

Personal.xls will open hidden from now on but macros will be available for
all open workbooks and sheets.

-Save it as an add-in (how?)

To save a workbook as an add-in you just open a new workbook and the Visual
Basic Editor. Create a module and copy all your macros to that module.

File>Save As>File Type Excel Add-in(*.xla)

- add it to your list of add-ins (how?)

Store your newly created add-in in Office\Library folder.

Then go to Tools>Add-ins and browse to the folder and check the add-in to
load it.

Leqve it loaded.


Gord Dibben MS Excel MVP