VBA Personal to VBA worksheet

[marshy3300]

Howdy,

I have some personal macros that only work if they are copied to the worksheet macro. Unfortunately I cant store them as a worksheet macro as this is imported from another macro and renamed to override the filename of an older version.

Twofold question, can i make a personal macro work as a worksheet one, or can i run another macro to copy the macros across?

Thanks in advance!

 

[Johnny C]

Without seeing them it's hard to answer your first question. There are a number of answers though.

1) re-write the macros so they work in any workbook that is activated (sorry if this sounds condescending but we've no idea of how complex the macros are - it might just need a variable adding with ActiveWorkbook.Name and Workbooks(variable).Activate)

It would help if you posted an example of the code, we could help you see if this were possible.

2) Put the macros into a new workbook, and save as an Add-In (.XLA/.XLAM) and add the add-in to your Excel. You can add the macro's to your quick access toolbar or a custom ribbon. (This works well with functions)

3) You can copy macros from within workbooks. It's not that difficult, but I'd go with the first 2 options before trying it.
This is the best resource to get you started :-
http://www.cpearson.com/excel/vbe.aspx
but read it all before you start. Be aware that, since a crude form of virus can be written using this functionality, some anti-virus scanners will delete any code that accesses the VBA project.
I once wrote a macro that once put into a file, completely replicated the file into a new workbook (complex pivot tables caused it to crash after an hour or so and saveAs just saved the corrupted file as a new corrupted file) and part of this replication was to add code to replicate the replication code into the new file.

 

[marshy3300]

Thanks Johnny - I took option 2 with a twist of exporting the macros to .BAS and then writing a macro to import both the ones I need

Cheers as I didnt think of something as simple!