sheet naming

[Phil]

I have a macro that copies a sheet then names it. It goes as follows:

Sub Createsheet()
Sheets("Sheet1").Select
Sheets("Sheet1").Copy Before:=Sheets(1)
Sheets("Sheet1 (2)").Select
Sheets("Sheet1 (2)").Name = "datasheet1"
Range("C4").Select
End Sub

Is there a way to make it so that when you run the macro again and create a
new sheet and it sees that datasheet1 already exists it will name the next
sheet datasheet2? And then when you run it again it sees that datasheet1 and
datasheet2 exists and it names the next one datasheet3?

Thanks,
Phil

 

[antoka05]

you can try so:

Sheets("foglio1").Select
Sheets("foglio1").Copy Before:=Sheets(1)
Sheets("foglio1 (2)").Select

On Error Resume Next
ctrNumber = 1
wsName = "ok"
Do While wsName <> ""
DoEvents
wsName = ""
wsName = ActiveWorkbook.Sheets("datasheet" & ctrNumber).Name
If wsName <> "" Then
ctrNumber = ctrNumber + 1
End If
Loop
On Error GoTo 0

Sheets("foglio1 (2)").Name = "datasheet" & ctrNumber
Range("C4").Select

 

[Bob Phillips]

Phil,

Here's a neat little way

Sub CreateSheet()
Dim idx As Long
idx = 1
On Error Resume Next
idx = Evaluate(ActiveWorkbook.Names("___idx").RefersTo)
On Error GoTo 0
Worksheets("Sheet1").Copy Before:=Worksheets(1)
ActiveSheet.Name = "datasheet" & idx
idx = idx + 1
ActiveWorkbook.Names.Add Name:="___idx", RefersTo:=idx
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Phil]

Thanks alot, much appreciated.

Phil

 

[Phil]

Thanks alot. Much appreciated. I gonna try both suggestions posted later
tonight when I get a chance.

Phil