List Worksheets that have a specific value in a cell

[John Sadler]

Hi folks .. I have a macro that lists (as hyperlinks) the Names of an array of Worksheets. Problem for me is this is not dynamic. The Worksheets relate to staff and when they leave I have to change the Array. In each Worksheet I have cell U1 that tells me if the Staff Member is Active or Inactive.

So, I would like to list the names of only the sheets where U1 = Active

This way I have a more dynamic macro that I don't have to change.

Hope someone can help me out please

Cheers John

 

[Norie]

John

Can you post your current code?

 

[John Sadler]

John

Can you post your current code?

Here you go:

Sub ListSalesRepSheets()

Application.ScreenUpdating = False
Dim ws As Worksheet
Dim cell As Range

'Sheets("Welcome").Select
'Range("B6").Select
'Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("B6").Select


For Each ws In Worksheets(Array("Annette Short", "Aaron Fidge", "Anthony Tavuileota", "Ash Gopalraj", "Bryce Scholes", "Jas Dhillon", "Dennis Eyck", "Gavin Churton", "Jim Walsh", "Patrick Matthews", "Richard White", "Domenic Crouch", "Siobhan Spratt"))


If ws.Visible = xlSheetVisible And ActiveSheet.Name <> ws.Name Then

ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'" & ws.Name & "'" & "!B6", TextToDisplay:=ws.Name
ActiveCell.Offset(1, 0).Select

End If

Next ws
Range("B4").Select
Application.ScreenUpdating = True
End Sub

 

[John Sadler]

Actually folks ... I worked it out so no help required. Thanks
Cheers John