Button to copy sheet, rename sheet sequencially.

[foxgguy2005]

I have a Setup sheet, which has a button to creat a new sheet, which actually copies a tamplet. When i click this button it calls the new tab "Tamplet (2)"
Is there a sub i can put in the copy routine to auto-rename the tabs sequencially?
Note number of tabs is based upon how many times the user clicks the new page button.

Thanks!
~Josh

[Patrick Molloy]

Option Explicit

Public Sub AddSheets()
Dim ws As Worksheet
Set ws = Worksheets("template")
ws.Copy Worksheets(1)
SetSheetName Worksheets(1)
End Sub
Private Sub SetSheetName(ws As Worksheet)
On Error Resume Next
Dim sname As String
Dim index As Long
index = 0
Do
Err.Clear
index = index + 1
sname = "template" & Format$(index, "000")
ws.Name = sname
Loop While Err.Number <> 0
End Sub

"foxgguy2005" wrote:

>
> I have a Setup sheet, which has a button to creat a new sheet, which
> actually copies a tamplet. When i click this button it calls the new
> tab "Tamplet (2)"
> Is there a sub i can put in the copy routine to auto-rename the tabs
> sequencially?
> Note number of tabs is based upon how many times the user clicks the
> new page button.
>
> Thanks!
> ~Josh
>
>
> --
> foxgguy2005
> ------------------------------------------------------------------------
> foxgguy2005's Profile: http://www.excelforum.com/member.php...o&userid=23663
> View this thread: http://www.excelforum.com/showthread...hreadid=378885
>
>

[foxgguy2005]

I tried putting this into my command button as follows:
but it does not work, i'm not quite sure what i'm doing wrong here?
Thanks!

Please Login or Register  to view this content.

[foxgguy2005]

anyone help me out on this one, much appreaciate it!

[Dave Peterson]

I put a commmandbutton on a worksheet, double clicked on that commandbutton and
pasted your code.

It worked right out of the box.

If you're using xl97, try changing the commandbutton's .takefocusonclick
property to false.

If that's not it, what happened when you tried it?

foxgguy2005 wrote:
>
> I tried putting this into my command button as follows:
> but it does not work, i'm not quite sure what i'm doing wrong here?
> Thanks!
>
> Code:
> --------------------
> Private Sub CommandButton1_Click()
> Dim ws As Worksheet
> Set ws = Worksheets("Tamplet")
> ws.Copy Worksheets(1)
> SetSheetName Worksheets(1)
> End Sub
> Private Sub SetSheetName(ws As Worksheet)
> On Error Resume Next
> Dim sname As String
> Dim index As Long
> index = 0
> Do
> Err.Clear
> index = index + 1
> sname = "Tamplet" & Format$(index, "000")
> ws.Name = sname
> Loop While Err.Number <> 0
>
> End Sub
> --------------------
>
> --
> foxgguy2005
> ------------------------------------------------------------------------
> foxgguy2005's Profile: http://www.excelforum.com/member.php...o&userid=23663
> View this thread: http://www.excelforum.com/showthread...hreadid=378885

--

Dave Peterson

[foxgguy2005]

yeah you know it works for mee to actually, i'm retarded, lol.

Thanks man, tweaked the code for only 2 index numbers and to not include the tamplet... works like a charm, you are the MAN!

[Dave Peterson]

I'd say Patrick was the man!

foxgguy2005 wrote:
>
> yeah you know it works for mee to actually, i'm retarded, lol.
>
> Thanks man, tweaked the code for only 2 index numbers and to not
> include the tamplet... works like a charm, you are the MAN!
>
> --
> foxgguy2005
> ------------------------------------------------------------------------
> foxgguy2005's Profile: http://www.excelforum.com/member.php...o&userid=23663
> View this thread: http://www.excelforum.com/showthread...hreadid=378885

--

Dave Peterson

[foxgguy2005]

heh yeah, actually thats what i meant! :-P
Thanks big pat!
you too dave, for making me check myself.

[foxgguy2005]

an another quick note is there a way to copy them before a certain sheet
IE: Before:=Sheets("Setup")

[Dave Peterson]

Yep.

Option Explicit
Public Sub AddSheets()
Dim ws As Worksheet
Set ws = Worksheets("template")
ws.Copy _
before:=Worksheets("setup")
SetSheetName ActiveSheet
End Sub
Private Sub SetSheetName(ws As Worksheet)
On Error Resume Next
Dim sname As String
Dim index As Long
index = 0
Do
Err.Clear
index = index + 1
sname = "template" & Format$(index, "000")
ws.Name = sname
Loop While Err.Number <> 0
End Sub


When you copy a sheet, it becomes the activesheet. So we just pass the
activesheet to the "setSheetName" subroutine.



foxgguy2005 wrote:
>
> an another quick note is there a way to copy them before a certain
> sheet
> IE: Before:=Sheets("Setup")
>
> --
> foxgguy2005
> ------------------------------------------------------------------------
> foxgguy2005's Profile: http://www.excelforum.com/member.php...o&userid=23663
> View this thread: http://www.excelforum.com/showthread...hreadid=378885

--

Dave Peterson