Copy VBA code into a workbook created by a macro

[Planner Rob]

Excel 2003.

I have some code which takes a number of worksheets from the open workbook
and copies them to a new workbook. It then manipulates the data and saves the
new workbook without altering the original.

I need to bring a macro from the original workbook across so that when the
new file is opened independantly the macro can be run.

Any suggestions?

 

[Mike H]

Hi,

The problem with that approach is that you then have 2 versions of the same
macro or maybe even more if you repeat the operation any of which can get out
of synch if you modify the macro.

Why don't you put this macro in personal.xls then you have a single copy
available to all workbooks.

Mike

 

[Dave Peterson]

I like Mike H's idea of separating the code from the workbooks that need the
macros. But I wouldn't use personal.xls.

I'd create a separate dedicated workbook for those macros--and only open it when
I needed it.

And if I needed to share the macros with another user, I could just share that
dedicated workbook. I wouldn't have to worry about any existing personal.xls
workbook that the other user has.

 

[norie]

Why not use a template with the code already in it?

Create a new workbook from that and then copy your data into it.

 

[Dave Peterson]

I find the problem with that is that every new workbook that I create using that
template has the code in it. And if the code has to change, I'll never be able
to find all the copies of all the workbooks that have that code.

With the separate workbook for just the code, I have a better chance.

 

[norie]

Dave

I can kind of understand that but without knowing what the OP's code
is it's hard to tell if it matters.

Is the code going to be changed in the future?

If so is it possible that the code could be rewritten in some way to
take into account changes?

 

[Dave Peterson]

If you've ever had co-workers or a boss, you'll realize that the code will
always change <vbg>. There's always one more thing to fix or add to make it
perfect.

And if the code is spread over 100's of workbooks (or more), I'm not sure how
anyone would know to find them to change them--even if the code could be
rewritten mechanically.

 

[norie]

Dave

I suppose I've been lucky, most of the positions I had where working
with one or two people on specific projects.

And if the job got done properly everybody was happy - though that
didn't mean things didn't change,

In fact sometimes on certain projects the targets and hence the code/
whatever changed on a daily basis.

As for writing code mechanically - that's not really what I meant.

I meant write code that will cope with the changes.

Whether that's an option for this OP is impossible to tell without
seeing the code or at least more information on what it's purpose is.

Oh, and you can write code that will write/delete/alter code but doing
so can bring up other issues.

One of those issues is security, which I imagine could be a major
issue when dealing with multiple users.

 

[Dave Peterson]

I don't recall many projects that were actually finished--well, if the project
was discarded, then I guess it was finished <vbg>.

In my experience, there was always a request for an enhancement.

And depending on what the changes could be, I guess you could have a user input
sheet where the user could keep track of important rules. But those would only
be as good as you thought of when you developed the program.

(Folder names, filenames, drives (UNC/Mapped) are all that kind of thing that
the user can modify and still stay out of code.)

 

[Planner Rob]

Thanks for the discussion on this. If I eloborate a little maybe you can
advise me on my best course of action.

The source workbook is large with many complex formaula populating
information into a number of sheets. It is used by a number of people and it
is regularly enhanced and altered, both the formula and the code. This means
the this source workbook is (should be!) tracked and therefore kept up to
date.

The secondary workbook takes data from the first and summarises it into rows
of data, which can then be represented on a chart. The data is 'fixed' on
output (not linked to source workbook). The code is required to allow the
selection of which rows of data will be plotted on charts (one chart for each
selected). The secondary workbook needs to be able to continue to operate if
it is passed to a user who does not have the source workbook.

By being able to transfer code from the source to the secondary, it will
always contain the latest version of the code. Previous versions will not
need updating.

Is it possible to transfer some code?

 

[Dave Peterson]

I'd still use an addin. I think it's safer and easier to control.

But if you want to try writing code that writes code, start by reading Chip
Pearson's site:
http://www.cpearson.com/excel/vbe.aspx

Or you may want to use the template idea.

 

[Planner Rob]

Thanks, I'll try and see where I get!

I'd still use an addin. I think it's safer and easier to control.

But if you want to try writing code that writes code, start by reading Chip
Pearson's site:
http://www.cpearson.com/excel/vbe.aspx

Or you may want to use the template idea.