Sheet naming question
 

As part of a series of macros, I name a new sheet that I open with the
current date with

ActiveSheet.Name = Date$

Occasionally I need to run this routine more than once in the same day,
which gives an error

Run time error "1004": Cannot rename a sheet to the same name as another
sheet . . ."

Is there a way to check if there is a sheet with the current date name, and
then name the new sheet with an appendage - for example 12-21-2003A,
12-21-2003B etc. I know very little VBA, so as specific of code as possible
would be greatly appreciated.

If that's unreasonably difficult, is there is a way to allow the macros to
continue past the error, without renaming the sheet in that case?

Thanks in advance

Sheet naming question
 

-----Original Message-----
As part of a series of macros, I name a new sheet that I
open with the
current date with

ActiveSheet.Name = Date$

Occasionally I need to run this routine more than once in
the same day,
which gives an error

Run time error "1004": Cannot rename a sheet to the same
name as another
sheet . . ."

Is there a way to check if there is a sheet with the
current date name, and
then name the new sheet with an appendage - for example
12-21-2003A,
12-21-2003B etc. I know very little VBA, so as specific
of code as possible
would be greatly appreciated.

If that's unreasonably difficult, is there is a way to
allow the macros to
continue past the error, without renaming the sheet in
that case?

Thanks in advance



.
Try this.

Right before the rename of the sheet, put in the code:

On Error Resume Next

then... your rename code

then... If Err 0 then
Sheet("whatever").name = "newnameappendage"
Endif
then

On Error Go To 0

The first On Error will allow the rename command to NOT
fail hard, and let you evaluate the error and do
something....give it another name....The on error go to 0
turns back on error checking so that future errors will be
caught.

hope this helps....the go to might need to be goto
instead..can't remember. The 'then's' on the left margin
are NOT code...just my comments

Steve

Sheet naming question
 

I'll give 2 ways:

1. This will add a sheet with today's name if none exists and do nothing
if one does.

You can refer to it prior to appending any data to today's sheet

Sub name_sheet_as_today()

dup = "no"

For Each sheet In Sheets()
If sheet.Name = date$ Then dup = "yes"
Next

If dup = "no" Then

Sheets.Add.name=date$

End If

End Sub


Later, you can activate this page by inserting:

sheets(date$).activate
or
sheets(date$).select

in your appropriate routine.

A second subroutine that is much shorter:

Sub name_with_error()

On Error Resume Next
Sheets.Add.Name = Date$

End Sub



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Sheet naming question
 

You could integrate Chip Pearson's function to test if a sheet exists...

Function WorksheetExists(WSName As String, Optional WB As Workbook =
Nothing) As Boolean
On Error Resume Next
WorksheetExists = CBool(Len(IIf(WB Is Nothing, ThisWorkbook,
WB).Worksheets(WSName).Name))
End Function

Sub Tester()
If WorksheetExists(Date$) = True Then
ActiveSheet.Name = Date$ & "A"
Else
ActiveSheet.Name = Date$
End If
End Sub

--
Regards,
Rocky McKinley


"Mark R" wrote in message
...
As part of a series of macros, I name a new sheet that I open with the
current date with

ActiveSheet.Name = Date$

Occasionally I need to run this routine more than once in the same day,
which gives an error

Run time error "1004": Cannot rename a sheet to the same name as another
sheet . . ."

Is there a way to check if there is a sheet with the current date name,
and
then name the new sheet with an appendage - for example 12-21-2003A,
12-21-2003B etc. I know very little VBA, so as specific of code as
possible
would be greatly appreciated.

If that's unreasonably difficult, is there is a way to allow the macros to
continue past the error, without renaming the sheet in that case?

Thanks in advance

Sheet naming question
 

On Error Resume Next
ActiveSheet.Name = Date$
On Error GoTo 0

Or more sopisticated:

Sub NameAsDate()
Dim s As String
s = Date$
If Not WorksheetExists(s) Then
ActiveSheet.Name = s
Else
Dim i As Integer
Do Until Not WorksheetExists(s)
s = Date$ & Chr(65 + i)
i = i + 1
Loop
ActiveSheet.Name = s
End If
End Sub

Function WorksheetExists(wsName As String, _
Optional wbName As String) As Boolean
If wbName = "" Then wbName = _
ActiveWorkbook.Name
On Error Resume Next
WorksheetExists = CBool(Len(Workbooks(wbName) _
.Worksheets(wsName).Name))
End Function

--

Vasant



"Mark R" wrote in message
...
As part of a series of macros, I name a new sheet that I open with the
current date with

ActiveSheet.Name = Date$

Occasionally I need to run this routine more than once in the same day,
which gives an error

Run time error "1004": Cannot rename a sheet to the same name as another
sheet . . ."

Is there a way to check if there is a sheet with the current date name,
and
then name the new sheet with an appendage - for example 12-21-2003A,
12-21-2003B etc. I know very little VBA, so as specific of code as
possible
would be greatly appreciated.

If that's unreasonably difficult, is there is a way to allow the macros to
continue past the error, without renaming the sheet in that case?

Thanks in advance

Sheet naming question
 

Wow, thanks to all!


"Mark R" wrote in message
...
As part of a series of macros, I name a new sheet that I open with the
current date with

ActiveSheet.Name = Date$

Occasionally I need to run this routine more than once in the same day,
which gives an error

Run time error "1004": Cannot rename a sheet to the same name as another
sheet . . ."

Is there a way to check if there is a sheet with the current date name,
and
then name the new sheet with an appendage - for example 12-21-2003A,
12-21-2003B etc. I know very little VBA, so as specific of code as
possible
would be greatly appreciated.

If that's unreasonably difficult, is there is a way to allow the macros to
continue past the error, without renaming the sheet in that case?

Thanks in advance

Sheet naming question
 

Thanks so much. This worked perfectly.


"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
On Error Resume Next
ActiveSheet.Name = Date$
On Error GoTo 0

Or more sopisticated:

Sub NameAsDate()
Dim s As String
s = Date$
If Not WorksheetExists(s) Then
ActiveSheet.Name = s
Else
Dim i As Integer
Do Until Not WorksheetExists(s)
s = Date$ & Chr(65 + i)
i = i + 1
Loop
ActiveSheet.Name = s
End If
End Sub

Function WorksheetExists(wsName As String, _
Optional wbName As String) As Boolean
If wbName = "" Then wbName = _
ActiveWorkbook.Name
On Error Resume Next
WorksheetExists = CBool(Len(Workbooks(wbName) _
.Worksheets(wsName).Name))
End Function

--

Vasant



"Mark R" wrote in message
...
As part of a series of macros, I name a new sheet that I open with the
current date with

ActiveSheet.Name = Date$

Occasionally I need to run this routine more than once in the same day,
which gives an error

Run time error "1004": Cannot rename a sheet to the same name as
another
sheet . . ."

Is there a way to check if there is a sheet with the current date name,
and
then name the new sheet with an appendage - for example 12-21-2003A,
12-21-2003B etc. I know very little VBA, so as specific of code as
possible
would be greatly appreciated.

If that's unreasonably difficult, is there is a way to allow the macros
to
continue past the error, without renaming the sheet in that case?

Thanks in advance