| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
List specific worksheets
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
List specific worksheets
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
List specific worksheets
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 ... 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
List specific worksheets
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 ... 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
List specific worksheets
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 ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to load a list of web links into specific worksheets? | Excel Worksheet Functions | |||
| How do I add specific categories from several worksheets? | Excel Worksheet Functions | |||
| if specific value from list A equals one of the values from list b... | Excel Worksheet Functions | |||
| Summing specific cells on two worksheets | Excel Worksheet Functions | |||
| For Each wks In ActiveWorkbook.Worksheets bar a specific one? | Excel Discussion (Misc queries) | |||
Linear Mode
