List Sheet Name if...

[cpadilla0024]

Hi,

I have created a button to list sheet names starting in the cell that I need but I only need ones that contain the word "Resource" in it. I am able to get it to go to the cell that I want or only pull Resource but not both together for some reason and it is killing me. Any help would be appreciated.

Nutshell: Looking for VBA code that says to list all worksheets that contain "Resource" starting in cell "A5"

Thanks!

 

[Chihiro]

So what's your current code?

 

[cpadilla0024]

The first one makes me do cell A1 when the button is pushed.

Private Sub ListAllSheets_Click()

ActiveSheet.Range("A5").Select

Dim i%, j%
j = 1
For i = 1 To Sheets.Count
If Sheets(i).Name Like "*Resource*" Then
Cells(j, 1).Value = Sheets(i).Name
j = j + 1
Else
End If
Next i

End Sub

Then this one I get an error:

Private Sub ListAllSheets_Click()

ActiveSheet.Range("A5").Select

Dim ws As Worksheet
Dim Counter As Integer

Counter = 0

For Each ws In ActiveWorkbook.Worksheets
If Sheet.Name Like "*Resource*" Then
    ActiveCell.Offset(Counter, 0).Value = ws.Name
    Counter = Counter + 1

Next ws


End Sub

 

[Chihiro]

You are missing End If in your second code (After Counter = Count + 1 line.)

In the first code j should start from either 5 or 6 depending on what row should the code start updating.

 

[cpadilla0024]

Cool. I got the first code to work.

I am curious about the second code though. Is everything right besides adding the "End If" statement. If so, what would I be saying? That was where I was getting lost.

Thanks for your help.

 

[Chihiro]

Oh one more thing about your second code.
Sheet should be replaced with ws.

 

[cpadilla0024]

It all works now. Thanks!

 

[Marc L]

Hi, playing with an array to write the results at once :​

• a demonstration for worksheets names containing 'Resource' :

Sub Demo1()
          Dim S$(), R&
    With ActiveWorkbook.Worksheets
        ReDim S(1 To .Count)
        For R = 1 To .Count:  S(R) = .Item(R).Name:  Next
    End With
        S = Filter(S, "Resource", True)
        If UBound(S) > -1 Then [A5].Resize(UBound(S) + 1).Value2 = Application.Transpose(S)
End Sub

• Close to your code a demonstration for worksheets names starting with 'Resource' :

Sub Demo2()
        Dim S$(), Ws As Worksheet, R&
      ReDim S(1 To ActiveWorkbook.Worksheets.Count, 0)
    For Each Ws In ActiveWorkbook.Worksheets
          If Ws.Name Like "Resource*" Then R = R + 1: S(R, 0) = Ws.Name
    Next
        If R Then [A5].Resize(R).Value2 = S
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​