|
Check for Sheet
I am copying a worksheet from an existing worksheet (in the same file) that
has been named ShtTransmissionDetail. When it is copied Excel creates a sheet named ShtTransmissionDetail1. The tab name of ShtTransmissionDetail1 is then set to the name a user has keyed in the system by the user. I am trying to check if this sheet exists, and if it does then delete it or copy over it. I have tried multiple different ways / syntax and received multiple different errors (object doesn't exist, etc...) and can't get this to work. Thanks. Sub CheckIfSheetExists() On Error Resume Next Application.DisplayAlerts = False If ShtTransmissionDetail1 Is Nothing Then Else ShtTransmissionDetail1.Delete End If End Sub |
Check for Sheet
This worked for me:
Sub Button1_Click() On Error Resume Next Application.DisplayAlerts = False If Sheets("Sheet1") Is Nothing Then Exit Sub Else Sheets("Sheet1").Delete End If Application.DisplayAlerts = True End Sub "Troubled User" wrote in message ... I am copying a worksheet from an existing worksheet (in the same file) that has been named ShtTransmissionDetail. When it is copied Excel creates a sheet named ShtTransmissionDetail1. The tab name of ShtTransmissionDetail1 is then set to the name a user has keyed in the system by the user. I am trying to check if this sheet exists, and if it does then delete it or copy over it. I have tried multiple different ways / syntax and received multiple different errors (object doesn't exist, etc...) and can't get this to work. Thanks. Sub CheckIfSheetExists() On Error Resume Next Application.DisplayAlerts = False If ShtTransmissionDetail1 Is Nothing Then Else ShtTransmissionDetail1.Delete End If End Sub |
Check for Sheet
Something like this perhaps?
dim wks as worksheet on error resume next set wks = ShtTransmissionDetail1 on error goto 0 Application.DisplayAlerts = False if not wks is nothing then wks.delete Application.DisplayAlerts = true -- HTH... Jim Thomlinson "Troubled User" wrote: I am copying a worksheet from an existing worksheet (in the same file) that has been named ShtTransmissionDetail. When it is copied Excel creates a sheet named ShtTransmissionDetail1. The tab name of ShtTransmissionDetail1 is then set to the name a user has keyed in the system by the user. I am trying to check if this sheet exists, and if it does then delete it or copy over it. I have tried multiple different ways / syntax and received multiple different errors (object doesn't exist, etc...) and can't get this to work. Thanks. Sub CheckIfSheetExists() On Error Resume Next Application.DisplayAlerts = False If ShtTransmissionDetail1 Is Nothing Then Else ShtTransmissionDetail1.Delete End If End Sub |
Check for Sheet
Thanks. This seems to have helped. I still have something strange going on
as it is creating the sheet called ShtTransmissionDetail2 even though I have deleted ShtTransmissionDetail1. Maybe the delete is not complete before I re-add? Anyway, I can code around it (although it is not what I expected). Thanks for your help! "Jim Thomlinson" wrote: Something like this perhaps? dim wks as worksheet on error resume next set wks = ShtTransmissionDetail1 on error goto 0 Application.DisplayAlerts = False if not wks is nothing then wks.delete Application.DisplayAlerts = true -- HTH... Jim Thomlinson "Troubled User" wrote: I am copying a worksheet from an existing worksheet (in the same file) that has been named ShtTransmissionDetail. When it is copied Excel creates a sheet named ShtTransmissionDetail1. The tab name of ShtTransmissionDetail1 is then set to the name a user has keyed in the system by the user. I am trying to check if this sheet exists, and if it does then delete it or copy over it. I have tried multiple different ways / syntax and received multiple different errors (object doesn't exist, etc...) and can't get this to work. Thanks. Sub CheckIfSheetExists() On Error Resume Next Application.DisplayAlerts = False If ShtTransmissionDetail1 Is Nothing Then Else ShtTransmissionDetail1.Delete End If End Sub |
Check for Sheet
The numbering of new sheets will not revert back to 1 until you close and
re-open the file. That is just the way it works... -- HTH... Jim Thomlinson "Troubled User" wrote: Thanks. This seems to have helped. I still have something strange going on as it is creating the sheet called ShtTransmissionDetail2 even though I have deleted ShtTransmissionDetail1. Maybe the delete is not complete before I re-add? Anyway, I can code around it (although it is not what I expected). Thanks for your help! "Jim Thomlinson" wrote: Something like this perhaps? dim wks as worksheet on error resume next set wks = ShtTransmissionDetail1 on error goto 0 Application.DisplayAlerts = False if not wks is nothing then wks.delete Application.DisplayAlerts = true -- HTH... Jim Thomlinson "Troubled User" wrote: I am copying a worksheet from an existing worksheet (in the same file) that has been named ShtTransmissionDetail. When it is copied Excel creates a sheet named ShtTransmissionDetail1. The tab name of ShtTransmissionDetail1 is then set to the name a user has keyed in the system by the user. I am trying to check if this sheet exists, and if it does then delete it or copy over it. I have tried multiple different ways / syntax and received multiple different errors (object doesn't exist, etc...) and can't get this to work. Thanks. Sub CheckIfSheetExists() On Error Resume Next Application.DisplayAlerts = False If ShtTransmissionDetail1 Is Nothing Then Else ShtTransmissionDetail1.Delete End If End Sub |
Check for Sheet
Jim,
That would have made sense to me, but it is now toggling between 1 and 2! "Jim Thomlinson" wrote: The numbering of new sheets will not revert back to 1 until you close and re-open the file. That is just the way it works... -- HTH... Jim Thomlinson "Troubled User" wrote: Thanks. This seems to have helped. I still have something strange going on as it is creating the sheet called ShtTransmissionDetail2 even though I have deleted ShtTransmissionDetail1. Maybe the delete is not complete before I re-add? Anyway, I can code around it (although it is not what I expected). Thanks for your help! "Jim Thomlinson" wrote: Something like this perhaps? dim wks as worksheet on error resume next set wks = ShtTransmissionDetail1 on error goto 0 Application.DisplayAlerts = False if not wks is nothing then wks.delete Application.DisplayAlerts = true -- HTH... Jim Thomlinson "Troubled User" wrote: I am copying a worksheet from an existing worksheet (in the same file) that has been named ShtTransmissionDetail. When it is copied Excel creates a sheet named ShtTransmissionDetail1. The tab name of ShtTransmissionDetail1 is then set to the name a user has keyed in the system by the user. I am trying to check if this sheet exists, and if it does then delete it or copy over it. I have tried multiple different ways / syntax and received multiple different errors (object doesn't exist, etc...) and can't get this to work. Thanks. Sub CheckIfSheetExists() On Error Resume Next Application.DisplayAlerts = False If ShtTransmissionDetail1 Is Nothing Then Else ShtTransmissionDetail1.Delete End If End Sub |
Check for Sheet
I have not looked into it too deeply but are you performing a save at some
point. That resets a bunch of things... -- HTH... Jim Thomlinson "Troubled User" wrote: Jim, That would have made sense to me, but it is now toggling between 1 and 2! "Jim Thomlinson" wrote: The numbering of new sheets will not revert back to 1 until you close and re-open the file. That is just the way it works... -- HTH... Jim Thomlinson "Troubled User" wrote: Thanks. This seems to have helped. I still have something strange going on as it is creating the sheet called ShtTransmissionDetail2 even though I have deleted ShtTransmissionDetail1. Maybe the delete is not complete before I re-add? Anyway, I can code around it (although it is not what I expected). Thanks for your help! "Jim Thomlinson" wrote: Something like this perhaps? dim wks as worksheet on error resume next set wks = ShtTransmissionDetail1 on error goto 0 Application.DisplayAlerts = False if not wks is nothing then wks.delete Application.DisplayAlerts = true -- HTH... Jim Thomlinson "Troubled User" wrote: I am copying a worksheet from an existing worksheet (in the same file) that has been named ShtTransmissionDetail. When it is copied Excel creates a sheet named ShtTransmissionDetail1. The tab name of ShtTransmissionDetail1 is then set to the name a user has keyed in the system by the user. I am trying to check if this sheet exists, and if it does then delete it or copy over it. I have tried multiple different ways / syntax and received multiple different errors (object doesn't exist, etc...) and can't get this to work. Thanks. Sub CheckIfSheetExists() On Error Resume Next Application.DisplayAlerts = False If ShtTransmissionDetail1 Is Nothing Then Else ShtTransmissionDetail1.Delete End If End Sub |
Check for Sheet
No save. It appears to serial always beginning at zero rather than at the
last created value. Thanks for your help. "Jim Thomlinson" wrote: I have not looked into it too deeply but are you performing a save at some point. That resets a bunch of things... -- HTH... Jim Thomlinson "Troubled User" wrote: Jim, That would have made sense to me, but it is now toggling between 1 and 2! "Jim Thomlinson" wrote: The numbering of new sheets will not revert back to 1 until you close and re-open the file. That is just the way it works... -- HTH... Jim Thomlinson "Troubled User" wrote: Thanks. This seems to have helped. I still have something strange going on as it is creating the sheet called ShtTransmissionDetail2 even though I have deleted ShtTransmissionDetail1. Maybe the delete is not complete before I re-add? Anyway, I can code around it (although it is not what I expected). Thanks for your help! "Jim Thomlinson" wrote: Something like this perhaps? dim wks as worksheet on error resume next set wks = ShtTransmissionDetail1 on error goto 0 Application.DisplayAlerts = False if not wks is nothing then wks.delete Application.DisplayAlerts = true -- HTH... Jim Thomlinson "Troubled User" wrote: I am copying a worksheet from an existing worksheet (in the same file) that has been named ShtTransmissionDetail. When it is copied Excel creates a sheet named ShtTransmissionDetail1. The tab name of ShtTransmissionDetail1 is then set to the name a user has keyed in the system by the user. I am trying to check if this sheet exists, and if it does then delete it or copy over it. I have tried multiple different ways / syntax and received multiple different errors (object doesn't exist, etc...) and can't get this to work. Thanks. Sub CheckIfSheetExists() On Error Resume Next Application.DisplayAlerts = False If ShtTransmissionDetail1 Is Nothing Then Else ShtTransmissionDetail1.Delete End If End Sub |
| All times are GMT +1. The time now is 12:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com