Macros Button To Create New Worksheet with Existing Template

jacksum336

New Member
Joined
Jun 16, 2015
Messages
10
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!
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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

New Member
Joined
Jun 16, 2015
Messages
10
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

New Member
Joined
Jun 16, 2015
Messages
10
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

Legend
Joined
May 15, 2003
Messages
29,536
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,511
Messages
5,596,581
Members
414,079
Latest member
Frills

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