• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

List Sheet Name if...

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!
 
The first one makes me do cell A1 when the button is pushed.

Code:
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:

Code:
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
 
Last edited:

Chihiro

Excel Ninja
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.
 
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.
 

Marc L

Excel Ninja
Hi, playing with an array to write the results at once :​
  • a demonstration for worksheets names containing 'Resource' :
Code:
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' :
Code:
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 !​
 
Top