Dim sh as Object
for each sh in wbExtrFrom.Sheets
if sh.Name = "Top" Then

--
Regards,
Tom Ogilvy

"davegb" wrote in message
ups.com...

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.