Rename sheet if current sheet name exists
 

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

Rename sheet if current sheet name exists
 

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

Rename sheet if current sheet name exists
 

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

--
Regards,

OssieMac

Rename sheet if current sheet name exists
 

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



"OssieMac" wrote:

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

--
Regards,

OssieMac

Rename sheet if current sheet name exists
 

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

"Stefi" wrote:

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

Rename sheet if current sheet name exists
 

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

--
Regards,

OssieMac


"Ron5440" wrote:

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



"OssieMac" wrote:

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

--
Regards,

OssieMac

Rename sheet if current sheet name exists
 

On dec. 15, 20:16, Ron5440 wrote:
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



"Stefi" wrote:
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- 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

Rename sheet if current sheet name exists
 

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

"OssieMac" wrote:

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

--
Regards,

OssieMac


"Ron5440" wrote:

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



"OssieMac" wrote:

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

--
Regards,

OssieMac