sheet naming

P

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
 
A

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
 
B

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

Phil

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

Phil
 
Top