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!
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

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.
 
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,164,281
Messages
5,836,373
Members
430,424
Latest member
ALHK022

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