Solved: Copy VBA code from ws to ws on insert...

Greetings,

i be new here.
need a little help please.
I have a workbook with a master sheet with vba code, how may I copy this vba code to a newly inserted sheet when a user interactively inserts a new worksheet?
what I have gives me two sheets, one that is a copy of the master with the code and just another new sheet without the code.

thank you for any help.

regards.

Good Evening.

In general, it becomes a little complicated to "insert code" through VBA because the VBE Environment is not fully trusting. Macros can be very volatile, so it's very restricted.

I did say restricted, though, not impossible. But to give you the easiest method... Are you the only person who will be utilizing this Workbook or will it be distributed to others?


The easiest method for copying the code is to copy the entire Worksheet using the Copy method on the Master Sheet.

Take care.
Scott

Scott,

thanks for the reply.

No, this wokbook will be distributed and multiple "save as" copies will be used globally. The code I have working within the ThisWorkbook is as follows...

Private Sub Workbook_NewSheet(ByVal Sh As Object)

Worksheets("Master").Copy after:=Worksheets(Worksheets.Count)

End Sub

the problem is this creates a copy of the Master with the code, but because it is within the Workbook_NewSheet function, I also get another new worksheet. Is there a way to prevent this additional worksheet from being created and only execute the Copy function?

Thanks.

Regards.

Evening again.

You are on the right track. The key to what you want to do is the Argument for the Event. The Sh Object enables you to access the new Worksheet that is created by default.

[VBA]

Private Sub Workbook_NewSheet(ByVal Sh As Object)
' Disable the Applications Alerts. If you do not disable
' this, you will get a MessageBox asking if you are sure
' you want to delete this sheet.
Application.DisplayAlerts = False
' Delete the sheet that is being created via the NewSheet
' Function.
Sh.Delete
' Re-enable the Application Alert to continue normal operation.
Application.DisplayAlerts = True
' Duplicate the Master Sheet and add it to the end of the Layout.
Worksheets("Master").Copy After:=Worksheets(Worksheets.Count)
End Sub
[/VBA]

Enjoy.
Scott

Scott,

believe it or not, that was the same idea I had but not sure if there was another "more common sense" method to do it.

But! I want to thank you for this and I will proceed with this method.
Just seems there could be another method of disabling the event action from occurring once the app is inside the event.

Side ???, why doesn't the intellissnce work with the sh object?
Earlier before asking this posting this question, I tried to see what methods were attached to the new worksheet object.

Thanks again.

Regards.

It is defined as object so that could be anything. Not sure why they did it that way. Change Object to Workbook in the parameter declaration, do your thing and before you press the compile button, change it back to Object.

You can dim a worksheet object and just use it if that is easier.
e.g.
[VBA]Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
Dim St As Worksheet
Set St = sh
MsgBox St.Name
End Sub[/VBA]

Thanks Ken,

That work excellent.
I work mosting with C++ and I do not understand why the Object type does not show the intellisense for this VBA type. But your method definitely did work. I'll have to remember to change the Object to a specific type and back again! Just one of those things.

Regards.