Checking a worksheet is present

[Guest]

I have a macro that relies on a worksheet being present, however if the sheet
is missing I need to reply with a message box to tell the person to copy in
the worksheet

If Worksheet ("data") = False Then
MsgBox "You need to copy of the data worksheet."
Else
Continue with Macro
End If

TIA
mmc308

 

[Guest]

Try something like this

Sub data()

For ws = 1 To Worksheets.Count

If Worksheets(ws).Name = "Data" Then GoTo ContinueMacro

Next ws

MsgBox ("You need to copy Data Worksheet")

ContinueMacro:
"ENTER REST OF MACRO HERE"

End Sub

 

[Dave Peterson]

Dim TestWks as worksheet

set testwks = nothing
on error resume next
set testwks = worksheets("data")
on error goto 0

if testwks is nothing then
msgbox "your message here
else
'keep going...
end if

 

[Guest]

Keep in mind, that if Data sheet isn't present, you have to create another
line after the msgbox if you don't want the macro to continue. One way is to
put GoTo NoData

Then right before End Sub

Put the Line NoData: MsgBox("No Data Worksheet")
End Sub

Whatever you like.... theres tons of ways to do it.

 

[JE McGimpsey]

One way:

Dim ws As Worksheet
On Error Resume Next
Set ws = ActiveWorkbook.Worksheets("data")
On Error GoTo 0
If ws Is Nothing Then
MsgBox "You need to copy of the data worksheet."
Else
'Continue with Macro
End If

 

[Guest]

This worked

Many Thanks for your help

mmc308

One way:

Dim ws As Worksheet
On Error Resume Next
Set ws = ActiveWorkbook.Worksheets("data")
On Error GoTo 0
If ws Is Nothing Then
MsgBox "You need to copy of the data worksheet."
Else
'Continue with Macro
End If