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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,329
Office Version
  1. 365
Platform
  1. Windows
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

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

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,329
Office Version
  1. 365
Platform
  1. Windows
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

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,160
Messages
5,768,527
Members
425,480
Latest member
br400821

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
Top