Check for dated sheet
 

I tried the code that Bob gave me and it still errors on a
second macro run. "Run-time error '1004' Cannot rename a
sheet to the same name as another sheet". It adds the
sheet just fine the first time, but if I rerun the macro
it croaks when the sheet name has already been created.

Sub blank1()
Dim oWs As Worksheet
On Error Resume Next
Set oWs = Sheets("Blank")
On Error GoTo 0
If Not oWs Is Nothing Then
oWs.Copy Befo=Sheets(1)
ActiveSheet.Name = Format(Date - 1, "mm-dd-yyyy")
End If
End Sub


Mike

Check for dated sheet
 

Hi,
Try this - errors if sheet already exists. I assume you only want one
sheet per day/date.

Sub blank1()

Dim oWs As Worksheet
Dim oSheet As Worksheet
Dim shName As String


Set oWs = Sheets("Blank")
On Error GoTo 0
If Not oWs Is Nothing Then
Set oSheet = Nothing
On Error Resume Next
shName = Format(Date - 1, "mm-dd-yyyy")
Set oSheet = Sheets(shName)
On Error GoTo 0
If oSheet Is Nothing Then
oWs.Copy Befo=Sheets(1)
ActiveSheet.Name = shName
Else
MsgBox " Sheet " & shName & " already exists"
End If
End If

End Sub.


HTH

"Mike K" wrote:

I tried the code that Bob gave me and it still errors on a
second macro run. "Run-time error '1004' Cannot rename a
sheet to the same name as another sheet". It adds the
sheet just fine the first time, but if I rerun the macro
it croaks when the sheet name has already been created.

Sub blank1()
Dim oWs As Worksheet
On Error Resume Next
Set oWs = Sheets("Blank")
On Error GoTo 0
If Not oWs Is Nothing Then
oWs.Copy Befo=Sheets(1)
ActiveSheet.Name = Format(Date - 1, "mm-dd-yyyy")
End If
End Sub


Mike

Check for dated sheet
 

your code checks to find a sheet named 'Blank'
If it does, it makes a copy and re-names the copy. The sheet called Blank
still exists, so when you run the code again, it makes another copy and tries
to name it again. Are you sure that you need to copy the sheet called balnk,
and not rename it?


Sub blank1()
Dim oWs As Worksheet
On Error Resume Next
Set oWs = Sheets("Blank")
On Error GoTo 0
If Not oWs Is Nothing Then
oWS.Name = Format(Date - 1, "mm-dd-yyyy")
End If
End Sub



"Mike K" wrote:

I tried the code that Bob gave me and it still errors on a
second macro run. "Run-time error '1004' Cannot rename a
sheet to the same name as another sheet". It adds the
sheet just fine the first time, but if I rerun the macro
it croaks when the sheet name has already been created.

Sub blank1()
Dim oWs As Worksheet
On Error Resume Next
Set oWs = Sheets("Blank")
On Error GoTo 0
If Not oWs Is Nothing Then
oWs.Copy Befo=Sheets(1)
ActiveSheet.Name = Format(Date - 1, "mm-dd-yyyy")
End If
End Sub


Mike

Check for dated sheet
 

Mike,

Sounds like you are trying to do it more than once a day. Try this if so

Sub blank1()
Dim oWs As Worksheet
On Error Resume Next
Set oWs = Sheets("Blank")
On Error GoTo 0
If Not oWs Is Nothing Then
oWs.Copy Befo=Sheets(1)
ActiveSheet.Name = Format(Date - 1, "mm-dd-yyyy hh-mm-ss")
End If
End Sub


--
HTH

Bob Phillips

"Mike K" wrote in message
...
I tried the code that Bob gave me and it still errors on a
second macro run. "Run-time error '1004' Cannot rename a
sheet to the same name as another sheet". It adds the
sheet just fine the first time, but if I rerun the macro
it croaks when the sheet name has already been created.

Sub blank1()
Dim oWs As Worksheet
On Error Resume Next
Set oWs = Sheets("Blank")
On Error GoTo 0
If Not oWs Is Nothing Then
oWs.Copy Befo=Sheets(1)
ActiveSheet.Name = Format(Date - 1, "mm-dd-yyyy")
End If
End Sub


Mike

Check for dated sheet
 

Bob,
The code you just posted is the exact same as the first one
you posted for me. I only ran it twice to account for the possibility
that someone will enter part of the data, close the workbook and
reopen it a little later. I thought it would be slick to have it
automatically copy the page when opening, as we used to do it
manually. The "Blank" worksheet is just a template sheet with a lot
of formulas, labels ect... I have a habit of partially inputting data
(for shifts 1 and 2) then going back towards the end of the 3rd shift
and entering the rest. If this were in the workbook open event it
would be transparent no matter how many times you open and close it.

Thanks,
Mike

On Thu, 28 Apr 2005 09:04:56 +0100, "Bob Phillips"
wrote:

Mike,

Sounds like you are trying to do it more than once a day. Try this if so

Sub blank1()
Dim oWs As Worksheet
On Error Resume Next
Set oWs = Sheets("Blank")
On Error GoTo 0
If Not oWs Is Nothing Then
oWs.Copy Befo=Sheets(1)
ActiveSheet.Name = Format(Date - 1, "mm-dd-yyyy hh-mm-ss")
End If
End Sub

Check for dated sheet
 

Mike,

It is not exactly the same, I added a time stamp so that if re-run on the
same day it wouldn't fail on an existing name.

--
HTH

Bob Phillips

wrote in message
...
Bob,
The code you just posted is the exact same as the first one
you posted for me. I only ran it twice to account for the possibility
that someone will enter part of the data, close the workbook and
reopen it a little later. I thought it would be slick to have it
automatically copy the page when opening, as we used to do it
manually. The "Blank" worksheet is just a template sheet with a lot
of formulas, labels ect... I have a habit of partially inputting data
(for shifts 1 and 2) then going back towards the end of the 3rd shift
and entering the rest. If this were in the workbook open event it
would be transparent no matter how many times you open and close it.

Thanks,
Mike

On Thu, 28 Apr 2005 09:04:56 +0100, "Bob Phillips"
wrote:

Mike,

Sounds like you are trying to do it more than once a day. Try this if so

Sub blank1()
Dim oWs As Worksheet
On Error Resume Next
Set oWs = Sheets("Blank")
On Error GoTo 0
If Not oWs Is Nothing Then
oWs.Copy Befo=Sheets(1)
ActiveSheet.Name = Format(Date - 1, "mm-dd-yyyy hh-mm-ss")
End If
End Sub