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

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.
--
Gary's Student
gsnu200712


"Mikey C" wrote:

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

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

On Mar 29, 3:37 pm, "Mikey C" <m.crin...@gmail.com> wrote:
> 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

Perfect - thanks Pete :-)

You're welcome - thanks for feeding back.

Pete

On Mar 30, 10:22 am, "Mikey C" <m.crin...@gmail.com> wrote:
> Perfect - thanks Pete :-)