List specific worksheets

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?

--
Adrian

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" wrote in message
...
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?

--
Adrian

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
--
Adrian


"Bernie Deitrick" wrote:

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" wrote in message
...
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?

--
Adrian

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" wrote in message
news
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
--
Adrian


"Bernie Deitrick" wrote:

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" wrote in message
...
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?

--
Adrian

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


"Bernie Deitrick" wrote:

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" wrote in message
news
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
--
Adrian


"Bernie Deitrick" wrote:

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" wrote in message
...
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?

--
Adrian

Dear Bernie,

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

Many, Many thanks

Cheers

--
Adrian


"Adrian Bear" wrote:

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


"Bernie Deitrick" wrote:

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" wrote in message
news
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
--
Adrian


"Bernie Deitrick" wrote:

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" wrote in message
...
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?

--
Adrian