If statement within For each ws in Workbook

[tp2468]

Hi All,

Trying to add worksheet names to a listbox based on a set criteria. The one line of code that doesn't work is.
If Workbooks(filename).ws.Cells(7, 2) = "Surname" Then

Not sure what it should be. ws is dimmed as worksheet

'I want worksheets added to the listbox only if cell(7,2) is equal to "Surname"
With Me.ListBox1
Me.ListBox1.Clear
    
    For Each ws In Workbooks(filename).Worksheets
        If Workbooks(filename).ws.Cells(7, 2) = "Surname" Then ' this line doesn't work.
        .AddItem ws.Name
        Else
        End If
    Next
End With

 

[Joe4]

Is this code running from a separate workbook than the one you are looping through?
If so, are both files open in the same Excel session at the time you are running this?

 

[Fluff]

That line should be

ws.Cells(7, 2) = "Surname" Then

The ws object is part of the relevant workbook, so you don't reference the workbook.

 

[tp2468]

That line should be

ws.Cells(7, 2) = "Surname" Then

The ws object is part of the relevant workbook, so you don't reference the workbook.

Works great.

Thanks

Tom

 

[Fluff]

You're welcome