How to make a macro create a macro in a new workbook

[ronnyfo]

Hi all - here's a challenge for ya:

Backgound:
I have a fairly densly packed excel workbook which contains data dumped from our planningsystem.
In this workbook i've made a lot of macros to automate various planning tasks, since the real planning system pretty much sucks, and its faster to dump the data to excel, automated the task and then just import it back into the planning tool..

However, this workbook has caught on, and now the engineering dep wants separate workbooks for each disciplines each week.
So instead of making a separate dump into excel from the planning tool for each discipline, i'd like to only make one dump to my workbook and then have a macro create/set up macros in the workbooks which is created for each discipline.

Problem:
So the question is:
How do i make a macro which creates a macro in another workbook?
Simplified & broken down:
1. Workbook A has a list of data
2. Workbook A has a macro which creates Workbook B and moves the data to this (This macro i already have)
3. Workbook A's macro also creates a macro in workbook B to i.e sort the list (independent of access to workbook A)


Any ideas will be greatly appreciated

cheers,
Ronny

 

[Norie]

Ronny

Why do you need code to create code?

Why not use a template workbook with all the code in place?

Then whenever you want to create a new workbook then use that as the basis.

Note I'm not saying it's not possible, check this Programming to the VBE.

The problem with using what's in that link is that it can raise some issues.

 

[ronnyfo]

Hi Norie - thanks for the quick reply

Hmm.. To be honest I didnt even consider using a template workbook with the macro and buttons,whistles n bells already in it..

Only downside( - If one could call it that..) with it, is that i have to have keep track of two workbooks then.. the mainwork book and the template workbook, but i am sure i could manage that..

Thanks


cheers,
Ronny

'course the engineer in me still wants know how i could do it the hard way

 

[ronnyfo]

Hi Norie - thanks for the quick reply

Hmm.. To be honest I didnt even consider using a template workbook with the macro and buttons,whistles n bells already in it..

Only downside( - If one could call it that..) with it, is that i have to have keep track of two workbooks then.. the mainwork book and the template workbook, but i am sure i could manage that..

Thanks


cheers,
Ronny

'course the engineer in me still wants know how i could do it the hard way

 

[Norie]

ronny

If you want to know how to do it the hard way check the link I posted.

PS Won't you be monitoring several workbooks anyway?

 

[ronnyfo]

Will do.

As for monitoring several workbooks... yes I do, but the less the better.
If the planning tool we use wasnt crap, i wouldn't have to use any workbooks to help with the planning.. but alas..