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

"Troubled User" <(E-Mail Removed)> wrote in message
news:252F2CA7-F960-4924-8C62-(E-Mail Removed)...
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

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

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

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

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

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

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