Looking for a sheet

[Guest]

I want to look though all open workbooks to see if a sheet exists. This is my
code so far:

Dim SDf As Workbook, SDws As Worksheet
For Each SDf In Application.Workbooks
Debug.Print SDf.Name
For Each SDws In Worksheets
Debug.Print SDws.Name
If SDws.Name = "ChemDraw1" Then
Sheets("ChemDraw1").Select
MsgBox ("Correct worksheet detected")
Exit For
End If
Next SDws
Next SDf

From the debug.print I can tell that it only looks through the sheets in the
active workbook. Can anybody help me out?

The Doctor

 

[Bob Phillips]

You need to qualify the worksheets collection with the workbook

Dim SDf As Workbook, SDws As Worksheet
For Each SDf In Application.Workbooks
Debug.Print SDf.Name
For Each SDws In SDf.Worksheets
Debug.Print SDws.Name
If SDws.Name = "ChemDraw1" Then
Sheets("ChemDraw1").Select
MsgBox ("Correct worksheet detected")
Exit For
End If
Next SDws
Next SDf