Forum Discussion
Solved
Excel VBA - Listing of Specifically-named Worksheets
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!
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
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.
