I have a macro that, depending on the data, will sometimes create a separate
sheet entitled "MC TABLE". I also have a second Macro within the same program
that will delete and clear the needed sections of the different worksheets so
that it can be ready for the next batch of data on the next day. The problem
is that the "MC TABLE" sheet needs to be deleted entirely but on the
occasions that it does not exist, I get an error message. How can I write the
code so that it tests to see if that sheet exists before attempting to select
or delete it??

Try this

You don't need to check, simply do this

Sub marine3()
Application.DisplayAlerts = False
On Error Resume Next
Sheets("MC TABLE").Delete
Application.DisplayAlerts = True
On Error GoTo 0
End Sub

But if uo want to

Sub marine()
Dim WS As Worksheet
On Error Resume Next
Set WS = Sheets("MC TABLE")
On Error GoTo 0
If Not WS Is Nothing Then
MsgBox "I exist"
Application.DisplayAlerts = False
WS.Delete
Application.DisplayAlerts = True
Else
MsgBox "I don't exist"
End If
End Sub


Mike

"Scott" wrote:

> I have a macro that, depending on the data, will sometimes create a separate
> sheet entitled "MC TABLE". I also have a second Macro within the same program
> that will delete and clear the needed sections of the different worksheets so
> that it can be ready for the next batch of data on the next day. The problem
> is that the "MC TABLE" sheet needs to be deleted entirely but on the
> occasions that it does not exist, I get an error message. How can I write the
> code so that it tests to see if that sheet exists before attempting to select
> or delete it??

Read the tips of John Walkenbach: very usefull!!!!!!

http://spreadsheetpage.com/index.php...vba_functions/

Success!!!

--
met vriendelijke groetjes

(E-Mail Removed)
"Scott" <(E-Mail Removed)> schreef in bericht
news:929BE3E9-CDEB-4BCE-AB03-(E-Mail Removed)...
>I have a macro that, depending on the data, will sometimes create a
>separate
> sheet entitled "MC TABLE". I also have a second Macro within the same
> program
> that will delete and clear the needed sections of the different worksheets
> so
> that it can be ready for the next batch of data on the next day. The
> problem
> is that the "MC TABLE" sheet needs to be deleted entirely but on the
> occasions that it does not exist, I get an error message. How can I write
> the
> code so that it tests to see if that sheet exists before attempting to
> select
> or delete it??

Thank you to both. Both answers proved useful for this question and another
issue I was working on.

Scott

"Scott" wrote:

> I have a macro that, depending on the data, will sometimes create a separate
> sheet entitled "MC TABLE". I also have a second Macro within the same program
> that will delete and clear the needed sections of the different worksheets so
> that it can be ready for the next batch of data on the next day. The problem
> is that the "MC TABLE" sheet needs to be deleted entirely but on the
> occasions that it does not exist, I get an error message. How can I write the
> code so that it tests to see if that sheet exists before attempting to select
> or delete it??

Hi Mike,

The short version worked great but was wondering what "On Error GoTo 0"
actually does?? The rest made sense to me such as "On Error Resume Next"
just means if an error occurs on a line then skip it and start with the next
line of code.

Thanks,

Scott


"Mike H" wrote:

> Try this
>
> You don't need to check, simply do this
>
> Sub marine3()
> Application.DisplayAlerts = False
> On Error Resume Next
> Sheets("MC TABLE").Delete
> Application.DisplayAlerts = True
> On Error GoTo 0
> End Sub
>
> But if uo want to
>
> Sub marine()
> Dim WS As Worksheet
> On Error Resume Next
> Set WS = Sheets("MC TABLE")
> On Error GoTo 0
> If Not WS Is Nothing Then
> MsgBox "I exist"
> Application.DisplayAlerts = False
> WS.Delete
> Application.DisplayAlerts = True
> Else
> MsgBox "I don't exist"
> End If
> End Sub
>
>
> Mike
>
> "Scott" wrote:
>
> > I have a macro that, depending on the data, will sometimes create a separate
> > sheet entitled "MC TABLE". I also have a second Macro within the same program
> > that will delete and clear the needed sections of the different worksheets so
> > that it can be ready for the next batch of data on the next day. The problem
> > is that the "MC TABLE" sheet needs to be deleted entirely but on the
> > occasions that it does not exist, I get an error message. How can I write the
> > code so that it tests to see if that sheet exists before attempting to select
> > or delete it??