Macros missing

[Angie M.]

Hi,

I'm using Excel 2003. I have a workbook file. I have several macros that I
created to work with this particular spreadsheet, they are stored in Module1
inside of this spreadsheet. They work great. I have since used this
spreadsheet to create 4 additional spreadsheets for different regions. So
now I have 5 workbooks containing data for 5 different regions, and they all
have their own copy of my macros. All of a sudden, when I try to use one of
the macros, it's looking for another one of the workbook files. When I open
the one it's looking for, I get a bogus error telling me it can't find the
macros.

How can I save this set of macros to Excel instead of saving to individual
workbooks? I would gladly keep them saved in each workbook but for some
reason they aren't working now. Please help. Thanks!!

 

[Jacob Skaria]

Angie

Refer the below link about excel Add-Ins
http://www.ozgrid.com/VBA/excel-add-in-create.htm

 

[Javed]

Hi,

I'm using Excel 2003.  I have a workbook file.  I have several macrosthat I
created to work with this particular spreadsheet, they are stored in Module1
inside of this spreadsheet.  They work great.  I have since used this
spreadsheet to create 4 additional spreadsheets for different regions.  So
now I have 5 workbooks containing data for 5 different regions, and they all
have their own copy of my macros.  All of a sudden, when I try to use one of
the macros, it's looking for another one of the workbook files.  When Iopen
the one it's looking for, I get a bogus error telling me it can't find the
macros.

How can I save this set of macros to Excel instead of saving to individual
workbooks?  I would gladly keep them saved in each workbook but for some
reason they aren't working now.  Please help.  Thanks!!

You can save the macro in Peronal Macro Workbook.

 

[Dave Peterson]

Start a new workbook
Copy that code into a new module in that new workbook's project.
Then save that new workbook with a nice name: AngieUtils.xls

Then open this file whenever you need to run the macro (or any of the macros
that you create in the future!).

You'll be able to activate your data workbook/worksheet and run your macro
(tools|macros|macro or alt-f8). (You could even use a shortcut key (if you have
a good memory!).)

Personally, I like a nicer interface--maybe a new item on the Worksheet Menu
bar--or a separate toolbar.

If you want to go this route, you could save that AngieUtils workbook as an
addin (it's an option in the file|saveAs dialog).

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

In xl2007, those toolbars and menu modifications will show up under the addins.

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm