macro to create a macro

V

vivian

Board Regular
Joined
Jul 1, 2004
Messages
50
i have created a spreadsheet to make a quote. I'm stuck as to how to make a macro so that when the quote turns to an order all that has to be done is open the quote that has been saved and hit a button that says "Transfer to Order".

The process is this: fill out the data entry form, then hit save and then a macro creates a new book in which the quote only is copied as values (no formulas).

Now what i want to happen is have a macro saved within this new book that has been created, that when the button that says "transfer to order" is pressed and only then, will another worksheet called "order" copy the quote worksheet to it and add another column to it beside the quoted price and add a formula.

I'm stuck because i can't figure out how to make a macro create a macro.

Is this possible or am i totally thinking something weird here? I want all of my formulas hidden in the original forms from the data entry sheet, and i want the person quoting to be able to save and retrieve the quote only or order files, so that's why i've made the quote transfer to a new sheet.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
Could you not save a workbook as a template, with the required macro to transfer to order. Then when you are getting the first macro to save as vaklued copy, get it to open the template, paste in there and then save as a different name?
 
Upvote 0
Thanks a bunch,

I created a quote template in which the macros are stored so it works very nicely to change the quote to an order.

I was having trouble transfering the valuesonly to the template originally because there were some merged cells that weren't allowing the values to copy over but for some reason the formulas copied over- but once i got rid of the merged cells and rearranged some stuff it worked very nicely.


Thanks again
 
Upvote 0
Could you post your code that you'd like created in another workbook? Also, the workbook you are wanting to create the Macro in...are you sure it has a Module in it already, or should we assume we need to create a Module as well?

Dave (y)

Edit:

After reading your post again, I can say yes, the workbook will have to have a Module created in it...I think. As an FYI, you will need this Object Reference checked:

Microsoft Visual Basic for Applications Extensibility X.X

Also, you will need to have "Trust Access To Visual Basic Project" enabled. To do this, go to the Tools menu in Excel, choose Macros, Security, then the "Trusted Sources" tab, and put a check next to "Trust Access To Visual Basic Project". Otherwise, you will get errors...

And since I'm editing...here is the code to add a Module to a workbook:

Code: 
Sub AddModule()

Dim VBComp As VBComponent

Set VBComp = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)
    VBComp.Name = "ModuleNew"
    Application.Visible = True
    
End Sub
 
Upvote 0
And...because this turned out to be fun and a learning experience...here is the code that will create this code:

Code: 
Sub lockSht()

    Dim mySht As Variant

    For Each mySht In ActiveWorkbook.Sheets
        mySht.Protect Password:="password", DrawingObjects:=True, _
            Contents:=True, Scenarios:=True
        mySht.EnableSelection = xlNoSelection
    Next

End Sub

in the module "ModuleNew" that the code from my previous post creates.

Here it is:

Code: 
Sub AddProcedure()

Dim VBCodeMod As CodeModule
Dim LineNum As Long

Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("NewModule").CodeModule

With VBCodeMod
    LineNum = .CountOfLines + 1
    .InsertLines LineNum, _
"Sub lockSht()" & vbCrLf & _
"   Dim mySht As Variant" & vbCrLf & vbCrLf & _
"    For Each mySht In ActiveWorkbook.Sheets" & vbCrLf & _
"        mySht.Protect Password:=""password"", DrawingObjects:=True, _" & vbCrLf & _
"            Contents:=True, Scenarios:=True" & vbCrLf & _
"        mySht.EnableSelection = xlNoSelection" & vbCrLf & _
"    Next" & vbCrLf & vbCrLf & _
"End Sub"

End With

End Sub

Dave
 
Upvote 0
You must log in or register to reply here.

Similar threads

zookeepertx
  • Question
Macro will run correctly in 1 workbook but not another
Replies
5
Views
483
zookeepertx
zookeepertx
S
  • Question
Add Customer Specific Code in front of Invoice number
Replies
5
Views
279
StevenRLaw
S
T
  • Question
VBA - Set time for a specific row using a Button
Replies
1
Views
406
ranman256
ranman256
S
  • Solved
Delete rows from another tab based on data in cells and keep formula
Replies
1
Views
288
rpaulson
rpaulson
T
  • Question
Update information in teams file from excel workbook
Replies
1
Views
312
Tanyaann1995
T

Forum statistics

Threads
1,214,985
Messages
6,122,606
Members
449,089
Latest member
Motoracer88

Share this page

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