Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying macros to another workbook
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying macros to another workbook
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying macros to another workbook
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros & Copying to another sheet | Excel Discussion (Misc queries) | |||
Macros - copying macros from one computer to another | Excel Discussion (Misc queries) | |||
copying macros | Excel Worksheet Functions | |||
copying macros | Excel Worksheet Functions | |||
Copying macros & tool bar | Excel Discussion (Misc queries) |