Code to create a new worksheet (based on an original pre-format sheet)

[Mikey C]

Hi all

I'm trying to create a command button which will copy an existing pre-
format worksheet (which I will probably hide) and insert the copied
sheet into the existing workbook but I have no idea how to do this! I
want to do this so users can create their own records without messing-
up the overall format (and make it simpler for them).

Can anybody help me?

Thanks

Mike

 

[Guest]

Sub newsheet()
n = Worksheets.Count
Sheets("Special").Copy After:=Sheets(n)
End Sub
Sub button_maker()
Application.CommandBars("Forms").Visible = True
ActiveSheet.Buttons.Add(290.25, 69, 51.75, 41.25).Select
Selection.OnAction = "newsheet"
Application.CommandBars("Forms").Visible = False
End Sub

button_maker just creates a typical "forms" button. Once run, it can be
discarded. newsheet will create a copy of a sheet called "Special" and put
it at the end. Modify as you require.

 

[Pete_UK]

Here's a snippet of code to copy the sheet "template" and then rename
it to "new_name":

my_temp = "template" ' <==change to suit
my_new_sheet = "new_name" ' <== change to suit

Sheets(my_temp).Select
Sheets(my_temp).Copy After:=Sheets(1)
'change sheet name
Sheets(my_temp & " (2)").Name = my_new_sheet

You will need to detect if a sheet with that name already exists (if
the User invokes the macro more than once).

Hope this helps.

Pete

 

[Mikey C]

Perfect - thanks Pete

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete