Rename sheet if current sheet name exists

[Ron5440]

A report is run from a web app.
My program opens the report and formats it.
I want to keep history so new sheets are added each time and named with
todays date.
The problem of course is if it is run twice in one day it crashes as it
tries to create the 2nd sheet with the same name.
I need something to the effect of: (Rdate is a mem var with my formatted date)
x=0
loop:
x=x+1
If a sheet named Rdate [currently exists] then
Rdate = Rdate & " (x)"
go to loop
else
activesheet.name = Rdate

 

[Stefi]

Try this:

Sub test()
x = 0
Do While SheetExists(Rdate)
x = x + 1
Rdate = Rdate & "(" & x & ")"
Loop
ActiveSheet.Name = Rdate
End Sub
Function SheetExists(Sh As String, _
Optional wb As Workbook) As Boolean
'-----------------------------------------------------------------
Dim oWs As Worksheet
If wb Is Nothing Then Set wb = ActiveWorkbook
On Error Resume Next
SheetExists = CBool(Not wb.Worksheets(Sh) Is Nothing)
On Error GoTo 0
End Function



--
Regards!
Stefi



„Ron5440†ezt írta:

 

[OssieMac]

Hi ron,

Sub NameWorkSheet()

Dim Rdate As String
Dim x As Integer
Dim strDateFormat As String

'Edit "dd-mm-yy" to required
'date format for worksheet
strDateFormat = "dd-mm-yy"
Rdate = Format(Date, strDateFormat)

Do
On Error Resume Next
Worksheets(Rdate).Select
If Err.Number > 0 Then
On Error GoTo 0
Exit Do 'Worksheet does not exist
Else
On Error GoTo 0
x = x + 1
Rdate = Format(Date, strDateFormat) _
& " (" & x & ")"
End If
Loop

'worksheet must be added after test to ensure
'that it is the active sheet for naming
Sheets.Add After:=Sheets(Sheets.Count)

ActiveSheet.Name = Rdate

End Sub

 

[Ron5440]

Thanks OssieMac...
I modified as such to make work...I used CurSht to set my current sheet name
after checking to see if the sheet existed to get back to the correct sheet.

Sheets.Add
Rdate = Month(Date) & " " & Day(Date) & " " & Year(Date)
CurSht = ActiveSheet.Name

Do
On Error Resume Next
Worksheets(Rdate).Select
If Err.Number > 0 Then
On Error GoTo 0
Worksheets(CurSht).Select
Exit Do 'Worksheet does not exist
Else
On Error GoTo 0
x = x + 1
Rdate = (Left(Rdate, 10) & " (" & x & ")")
End If
Loop
ActiveSheet.Name = Rdate

 

[Ron5440]

Thanks Stefi for the reply.
I tried the top portion of this but it did not work without the bottom
portion and I did not understand how to change the function portion to work
for my example.
It did help me get to my solution though.

Ron

Try this:

Sub test()
x = 0
Do While SheetExists(Rdate)
x = x + 1
Rdate = Rdate & "(" & x & ")"
Loop
ActiveSheet.Name = Rdate
End Sub
Function SheetExists(Sh As String, _
Optional wb As Workbook) As Boolean
'-----------------------------------------------------------------
Dim oWs As Worksheet
If wb Is Nothing Then Set wb = ActiveWorkbook
On Error Resume Next
SheetExists = CBool(Not wb.Worksheets(Sh) Is Nothing)
On Error GoTo 0
End Function



--
Regards!
Stefi



„Ron5440†ezt írta:

A report is run from a web app.
My program opens the report and formats it.
I want to keep history so new sheets are added each time and named with
todays date.
The problem of course is if it is run twice in one day it crashes as it
tries to create the 2nd sheet with the same name.
I need something to the effect of: (Rdate is a mem var with my formatted date)
x=0
loop:
x=x+1
If a sheet named Rdate [currently exists] then
Rdate = Rdate & " (x)"
go to loop
else
activesheet.name = Rdate

 

[OssieMac]

Hi again Ron,

Thanks for the feedback. However, using your method for formatting the
worksheet name returns only a single digit for days 1 to 9 resulting in the
left 10 characters returning a space on the end effectively creating 2 spaces
between the date and the number in brackets. If you use the format function
you can control this to return 2 digits for the day.

Also, I have modified the code again to create a worksheet variable in lieu
of a string for the added sheet name. That way, you do not have to select the
sheet to name it; just refer to it by the variable.

Sub NameWorkSheet2()
Dim CurSht As Worksheet
Dim Rdate As String
Dim x As Integer

Sheets.Add
Set CurSht = ActiveSheet

'Format function permits any valid date format
'and guarantees the number of digits if dd is
'used for the day.
Rdate = Format(Date, "mm dd yyyy")

Do
On Error Resume Next
Worksheets(Rdate).Select
If Err.Number > 0 Then
On Error GoTo 0
Exit Do 'Worksheet does not exist
Else
On Error GoTo 0
x = x + 1
Rdate = (Left(Rdate, 10) & " (" & x & ")")
End If
Loop

'with the worksheet assigned to a variable there is
'no need for it to be the active sheet to reference it.
CurSht.Name = Rdate

End Sub

 

[Stefi]

Thanks Stefi for the reply.
I tried the top portion of this but it did not work without the bottom
portion and I did not understand how to change the function portion to work
for my example.
It did help me get to my solution though.

Ron

Try this:

Sub test()
    x = 0
    Do While SheetExists(Rdate)
        x = x + 1
        Rdate = Rdate & "(" & x & ")"
    Loop
    ActiveSheet.Name = Rdate
End Sub
Function SheetExists(Sh As String, _
                     Optional wb As Workbook) As Boolean
'-----------------------------------------------------------------
    Dim oWs As Worksheet
    If wb Is Nothing Then Set wb = ActiveWorkbook
    On Error Resume Next
    SheetExists = CBool(Not wb.Worksheets(Sh) Is Nothing)
    On Error GoTo 0
End Function
„Ron5440” ezt írta:

A report is run from a web app.
My program opens the report and formats it.
I want to keep history so new sheets are added each time and named with
todays date.
The problem of course is if it is run twice in one day it crashes as it
tries to create the 2nd sheet with the same name.
I need something to the effect of: (Rdate is a mem var with my formatted date)
x=0
loop:
x=x+1
If a sheet named Rdate [currently exists] then
  Rdate = Rdate & " (x)"
  go to loop
else
  activesheet.name  = Rdate- Idézett szöveg elrejtése -

- Idézett szöveg megjelenítése -

Thanks for the feedback. You don't need to change the Sheetexists
function at all.
Stefi

 

[Ron5440]

OssieMac,

Hey, your correct! (of course)
Sorry for the delayed response as I have not been back to this post since I
got the answer ( or thought I had). It worked fine through the end of
December then when I came back on Jan 4th I found the problem. I had already
made the change that you are giving me here.

Thanks again,

RR