Check for Sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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

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
 
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
 
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!
 
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...
 
I have not looked into it too deeply but are you performing a save at some
point. That resets a bunch of things...
 
No save. It appears to serial always beginning at zero rather than at the
last created value.

Thanks for your help.
 
Back
Top