I have a monster file with a bunch of macros that I want to move to another
workbook so that the original file won't take so long to save. The workbook I
am moving the macros to will always be open in order to access the macros. My
question is what is the best way to do this?

Here's what I'm wondering...
- Should I manually copy all the code for each macro and paste it over?
- Is there an easy way in Visual Basic to drag and drop from one workbook to
another?
- Does the amount of modules effect how quickly a file saves?
- As there any limit to the amount of macros that can be put in one module
and is there any benefit to doing that in terms of speed of execution and how
long it takes for the workbook to save?

Many many thanks!

David

I would use windows explorer to copy that giant workbook with all the macros to
a new name.

Then open that new workbook and add a new worksheet and delete all the others.

Now all I'd have left is the macro and an empty sheet.

Then save this file as your new utility workbook (DaveUtil.xls). Then open that
when you need to run the macro.

If it's at all possible, you're life will be much easier if you separate the
data from the macros.

And I would go a step further.

I'd save this file as an addin--but that means that the macros won't show up in
the tools|macros dialog. I'd have to give the user (me???) a way to access
those procedures.

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

dpatcep wrote:

I have a monster file with a bunch of macros that I want to move to another
workbook so that the original file won't take so long to save. The workbook I
am moving the macros to will always be open in order to access the macros. My
question is what is the best way to do this?

Here's what I'm wondering...
- Should I manually copy all the code for each macro and paste it over?
- Is there an easy way in Visual Basic to drag and drop from one workbook to
another?
- Does the amount of modules effect how quickly a file saves?
- As there any limit to the amount of macros that can be put in one module
and is there any benefit to doing that in terms of speed of execution and how
long it takes for the workbook to save?

Many many thanks!

David

--

Dave Peterson

Another point to add to Dave's suggestions...................

Make sure the macros are not hard-coded to particular sheets or a workbook
by name.

Need lots of "activesheet" and "activeworkbook"


Gord Dibben MS Excel MVP

On Wed, 09 Sep 2009 14:00:38 -0500, Dave Peterson
wrote:

I would use windows explorer to copy that giant workbook with all the macros to
a new name.

Then open that new workbook and add a new worksheet and delete all the others.

Now all I'd have left is the macro and an empty sheet.

Then save this file as your new utility workbook (DaveUtil.xls). Then open that
when you need to run the macro.

If it's at all possible, you're life will be much easier if you separate the
data from the macros.

And I would go a step further.

I'd save this file as an addin--but that means that the macros won't show up in
the tools|macros dialog. I'd have to give the user (me???) a way to access
those procedures.

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

dpatcep wrote:

I have a monster file with a bunch of macros that I want to move to another
workbook so that the original file won't take so long to save. The workbook I
am moving the macros to will always be open in order to access the macros. My
question is what is the best way to do this?

Here's what I'm wondering...
- Should I manually copy all the code for each macro and paste it over?
- Is there an easy way in Visual Basic to drag and drop from one workbook to
another?
- Does the amount of modules effect how quickly a file saves?
- As there any limit to the amount of macros that can be put in one module
and is there any benefit to doing that in terms of speed of execution and how
long it takes for the workbook to save?

Many many thanks!

David