Renaming a Worksheet with Code Q

  • Thread starter Thread starter John
  • Start date Start date
J

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
 
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)
 
Thanks Bob works perfectly

John


Bob Phillips said:
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)
Click to expand...
 
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
 
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
 
Back
Top