CODEWITHSUNDEEP
vbaexcel
cplus
cplus

Reputation: 1115

List the names of specific worksheets

In the current workbook, I want to make a list of the sheets ending in -A and -B and put them in the sheet called List starting from cell C3:
my code is: 

Sub Make_list_of_sheets()
   Dim Sheet As Worksheet

   For i = 1 To sheets.Count

   If Sheet.Name Like "*" & strSearch & "-A" Or _
         Sheet.Name Like "*" & strSearch & "-B" Then
         Sheet(Sheet.Name).Count
         sheets("list").Range("C3")(i, 1).Value = sheets(i).Name
      End If
   Next i
End Sub


Problem: the code is not working, its runs into error.
How to correct this code?

Upvotes: 0

Views: 57

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Try:

Sub Make_list_of_sheets()
   Dim Sheet As Worksheet, j As Long
   Dim i As Long
   j = 3
   For i = 1 To Sheets.Count
    v = Right(Sheets(i).Name, 2)
    If v = "-B" Or v = "-A" Then
         Sheets("list").Cells(j, 3).Value = Sheets(i).Name
         j = j + 1
    End If
   Next i
End Sub

Note:

I assumed list rather than List

Upvotes: 1

Related Questions