for each ws in wbExtrFrom.worksheets
Should work ok.

But you could use another technique, too:

dim ws as worksheet
set ws = nothing
on error resume next
set ws = wbextrfrom.worksheets("Top")
on error goto 0

if ws is nothing then
'it doesn't exist
else
'it does exist
end if

And the second version doesn't care about upper/lower case for the worksheet
name.

But if you really want the user to delete it, maybe just deleting it in code
would be sufficient.

application.displayalerts = false
on error resume next
wbextrfrom.worksheets("top").delete
on error goto 0
application.displayalerts = true



davegb wrote:

The code I'm writing requires me to check to see if a worksheet named
"Top" already exists before the macro creates one. My test for a
sheet named Top isn't working because if I dim ws as a worksheet or
worksheets or an object, I get an "Object doesn't support that
property or method" error on the marked line. If I dim ws as sheets,
I get a compile error on the .name, "Method or data member not
found" since Sheets doesn't have a name property. Catch 22?
wbExtrFrom is declared as a workbook.

For Each ws In wbExtrFrom ß Object doesn't support this property or
method
If ws.Name = "Top" Then ß Method or data member not found
If MsgBox("A worksheet named Top already exists in this
workbook." _
& "Please remove or rename it and run the macro again.", _
vbOKOnly) = vbOK Then Exit Sub
End If
Next

What is the trick to workaround this limitation in VBA?

Thanks as always.

--

Dave Peterson