SOLVED

Excel VBA - Listing of Specifically-named Worksheets

Copper Contributor

Hello everyone.  I am using the following code to create a list of worksheets whose names contain "(M)" in their name:

 

Sub List_Worksheets()

 

   ' Clear existing list
   Range("AC1").Select
   Range(Selection, Selection.End(xlDown)).Select
   Selection.ClearContents

   ' Create list of specific worksheet names
   For i = 1 To Sheets.Count - 3
   Cells(i, 29) = Sheets(i).Name Like "* (M)"
   Next i

 

End Sub

 

What I get in return is a list in column AC containing "True" or "False" corresponding to the worksheet names that would otherwise be listed.  In other words, if the first worksheet name has "(M)" in it, "True" will show up first in the list.  If the second worksheet name does not have "(M)" in it, "False" will show up second in the list, and so on.

 

Can someone help me fix this?

 

Much appreciated!

2 Replies
best response confirmed by RShaw1972 (Copper Contributor)
Solution

@RShaw1972 

Like this:

Sub List_Worksheets()
    Dim i As Long
    Dim r As Long

    Application.ScreenUpdating = False

    ' Clear existing list
    Range("AC:AC").ClearContents

    ' Create list of specific worksheet names
    For i = 1 To Sheets.Count - 3
        If Sheets(i).Name Like "* (M)" Then
            r = r + 1
            Range("AC" & r).Value = Sheets(i).Name
         End If
    Next i

    Application.ScreenUpdating = True
End Sub
Worked beautifully! Thank-you.
1 best response

Accepted Solutions
best response confirmed by RShaw1972 (Copper Contributor)
Solution

@RShaw1972 

Like this:

Sub List_Worksheets()
    Dim i As Long
    Dim r As Long

    Application.ScreenUpdating = False

    ' Clear existing list
    Range("AC:AC").ClearContents

    ' Create list of specific worksheet names
    For i = 1 To Sheets.Count - 3
        If Sheets(i).Name Like "* (M)" Then
            r = r + 1
            Range("AC" & r).Value = Sheets(i).Name
         End If
    Next i

    Application.ScreenUpdating = True
End Sub

View solution in original post