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

ronnyfo

New Member
Joined
Jun 26, 2007
Messages
18
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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.
 
Upvote 0
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 :)
 
Upvote 0
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 :)
 
Upvote 0
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?
 
Upvote 0
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..
 
Upvote 0

Forum statistics

Threads
1,218,593
Messages
6,143,380
Members
450,484
Latest member
ChrisMac1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top