Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check inputdate, then retrieve data to fill in chart from dated co | Excel Worksheet Functions | |||
Check Activesheet for chart sheet or work sheet | Charts and Charting in Excel | |||
Enable check box in protected sheet + group check boxes | Excel Discussion (Misc queries) | |||
how to use sumif function to check date in 1 sheet is < 2 sheet | Excel Worksheet Functions | |||
Dated Fields | Excel Programming |