| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Check Activesheet for chart sheet or work sheet | Charts and Charting in Excel | |||
| Check if the first sheet is the selected sheet | Excel Programming | |||
| 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 | |||
| Check for sheet name | Excel Programming | |||
Hybrid Mode
