Renaming a Worksheet with Code Q

[John]

I am trying to name a new worksheet that is automatically created using the
value of a certain field i.e. Ll2 and then adding _GJRL to the end. I've
worked out how to do this and typically the new worksheet name is
April'04_GJRL.

strSheetName = Worksheets(ActiveSheet.Name).Range("l2")

Sheets.Add
ActiveSheet.Name = strSheetName & "_GJRL"
ActiveSheet.Select
ActiveSheet.Move After:=Sheets("Template")

However there maybe several "GJRL" in the month and as I want to save each
worksheet I have to manually rename each with something like April'04_GJRL#1
etc etc.

Is there any way I can write code to increase the name automatically eg.
April'04_GJRL#1 then April'04_GJRL#2 etc etc?

Thanks

John

 

[Bob Phillips]

Hi John,

Here is some code

Dim oWS As Worksheet
Dim i As Long
Dim sName As String
Dim fCreated As Boolean

On Error Resume Next
i = 1
fCreated = False
Do
sName = ActiveSheet.Range("L2") & "GIRL#" & Format(i, "00")
Set oWS = Worksheets(sName)
If oWS Is Nothing Then
Worksheets.Add.Name = sName
fCreated = True
End If
Set oWS = Nothing
i = i + 1
Loop Until fCreated
On Error GoTo 0


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[John]

Thanks Bob works perfectly

John

Hi John,

Here is some code

Dim oWS As Worksheet
Dim i As Long
Dim sName As String
Dim fCreated As Boolean

On Error Resume Next
i = 1
fCreated = False
Do
sName = ActiveSheet.Range("L2") & "GIRL#" & Format(i, "00")
Set oWS = Worksheets(sName)
If oWS Is Nothing Then
Worksheets.Add.Name = sName
fCreated = True
End If
Set oWS = Nothing
i = i + 1
Loop Until fCreated
On Error GoTo 0


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

This is what I've been looking for. I'm very beginner. Can you show me how to coded.

I wanted create a new worksheet within a workbook that will show the consecutive date

ei: April 1, April 2, April 3, and so o

instead of renaming the worksheet

Thanks in advance

----- John wrote: ----

Thanks Bob works perfectl

Joh

 

[Vasant Nanavati]

For i = 30 To 1 Step -1
Worksheets.Add.Name = "April " & i
Next

 

[Guest]

Thanks for the help but I do not know how to code it
Can someone give me a step by step instructions of how to code it and I do mean steps by steps.

Like what to click, which box to look for, etc..

Thanks very much in advance for helping!!!

Ma