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

M

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
 
G

Gary''s Student

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.
 
P

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
 
Top