Thanks!
~Josh
--
foxgguy2005
------------------------------------------------------------------------
foxgguy2005's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23663
View this thread: http://www.excelforum.com/showthread.php?threadid=378885
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
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
--------------------
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?
--
Dave Peterson
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
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.
--
Dave Peterson