List specific worksheets

[Adrian Bear]

I can produce a list of ALL worksheets in a workbook. How can I produce a
list limited to, say, a specific text string in a particular cell in each
worksheet?

 

[Bernie Deitrick]

Dim WS As Worksheet
For Each WS In ActiveWorkbook.Worksheets
If WS.Range("A1").Value Like "*Adrian* Then
'Add WS.Name to your list here
End If
Next WS

HTH,
Bernie
MS Excel MVP

 

[Adrian Bear]

Dear Bernie,

Many thanks for the speedy response.

However, apologies for being dense, but I still have problems.

I'm using Excel 2003 (should have mentioned that at the start)

In a worksheet named "List Sheets" I have a command button which, when
clicked, should run the code you sent - putting the list on the same
worksheet starting from cell c2 (say) where I place the cursor

So, I have the following:

Private Sub CommandButton2_Click()
Dim WS As Worksheet
For Each WS In ActiveWorkbook.Worksheets
If WS.Range("B32").Value Like ("Y") Then
'Add WS.Name '
End If
Next WS

End Sub

Where am I going wrong?

Again thanks

 

[Bernie Deitrick]

Adrian,

You're not actually listing the sheets: that was code that I thought you had already developed.
Here is my take:

Private Sub CommandButton2_Click()
Dim WS As Worksheet
Dim i As Integer
Dim myR As Long
Dim myC As Integer

myR = ActiveCell.Row
myC = ActiveCell.Column

For Each WS In ActiveWorkbook.Worksheets
If WS.Range("B32").Value = "Y" Then
Worksheets("List Sheets").Cells(myR, myC).Value = WS.Name
myR = myR + 1
End If
Next WS

End Sub

HTH,
Bernie
MS Excel MVP

 

[Adrian Bear]

Dear Bernie,

I'm grateful for all of your trouble. I tried your code and got:

run-time error '9'
subscript out of range

The debugger highlighted the line below "Then"

Sorry!

 

[Adrian Bear]

Dear Bernie,

Ignore the last message - it works fine and I'm eternally grateful!

Many, Many thanks

Cheers