View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Ron5440 Ron5440 is offline
external usenet poster
 
Posts: 16
Default 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