Macros Button To Create New Worksheet with Existing Template

[jacksum336]

Hi,

I would like to create a button and assign it a macros.
The macros would insert a new sheet at the end of my workbook that has an existing template.
The existing template worksheet's current file name is Labor. My workbook name is LaborTracking.

I did the following. I created a button using developer.
I hit Alt+11 to open up the VBA editor and created a new module.
Inside the module, I coded the following after looking through some forums:

Sub CopySheet()
Dim MySheetName As String
MySheetName = Labor.Text

Sheets("MasterSheet").Copy After:=Sheets("MasterSheet")
ActiveSheet.Name = MySheetName
End Sub


Thank you for your help!

 

[Smitty]

Welcome to the Board!

I'm not sure what "Labor.Text" is supposed to be, but this will copy the Labor sheet and rename it incrementally:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> CopySheet()<br>    <SPAN style="color:#00007F">Dim</SPAN> MySheetName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> SheetCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    SheetCount = Sheets.Count<br>    MySheetName = "Labor" & SheetCount<br><br>    Sheets("Labor").Copy After:=Sheets(SheetCount)<br>    ActiveSheet.Name = MySheetName<br>    <br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[jacksum336]

Hi Smitty,

Thanks for your quick response.

I don't know if I am doing this right.
I opened VBA editor and created a new module.
I inserted your code.
I created a command button.
The button does not work or do anything?

Am I missing a step? Thanks!

 

[jacksum336]

Hi Smitty,

I tried a second process to no avail.
After creating the command button, I put it in design mode and pressed view code.
I inputted the code provided, but the button still does not work. Thank you.

 

[Smitty]

If you created an ActiveX control the code will need to go in the button's Click event:

Private Sub CommandButton1_Click()
' Code
End Sub

The easiest thing to do is draw a shape and the assign the macro to it by right-clicking on the button. To test it you can hit F9 on the SheetCount = Sheets.Count line, when the code is activated it will stop there and you can step through it with F8.