Sheet naming question

  • Thread starter Thread starter Mark R
  • Start date Start date
M

Mark R

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
 
-----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.
Click to expand...

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
 
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
 
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
 
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
 
Thanks so much. This worked perfectly.


Vasant Nanavati said:
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
Click to expand...
 
Back
Top