Macro to list sheet names that match a criterion

[Jmoz092]

After searching for examples, I found a code that I'm trying to manipulate to fit my needs here: example

I'm trying to make a list (as column headers) of all the worksheets in a summary workbook for all of the worksheets that have a MM-DD-YY format.

I can get the code to work, and list the names of all the worksheets, but I can't make it not display the data and reference sheets contained in the workbook.

this works, but lists the data sheets in the header row also:

Sub SheetNamesAsColumns()
 
Dim ws As Worksheet
Dim x As Integer
 x = 1
For Each ws In Worksheets
     Sheets("Index").Cells(1, x) = ws.Name
     x = x + 1
Next ws
End Sub

This one does not work, as I try to sort out the non MM-DD-YY worksheets:

Sub SheetNamesAsColumns()
 
Dim ws As Worksheet
Dim x As Integer


x = 1
For Each ws In Worksheets
If ws.Name Like "**-**-**" Then
     Sheets("Index").Cells(1, x) = ws.Name
     x = x + 1
Next ws
End If
 
End Sub

What mistakes am I making the 2nd code, and how can I correct them?

 

[Joe4]

The End If needs to come become before the Next ws line.

 

[Roderick_E]

Your endif is outside your for-loop

 

[Jmoz092]

Thank you both, always something simple. It's working as intended now.

Sub SheetNamesAsColumns()
 
Dim ws As Worksheet
Dim x As Integer


x = 2
For Each ws In Worksheets
If ws.Name Like "**-**-**" Then
     Sheets("Index").Cells(1, x) = ws.Name
     x = x + 1
End If
Next ws


End Sub

 

[Joe4]

You are welcome.

Just remember that in closing block of code (like IF ... THEN, FOR ... NEXT, WITH ... END WITH), you close them out in the reverse order you opened them.
So the last one started is the first one to close.

 

[Jmoz092]

You are welcome.

Just remember that in closing block of code (like IF ... THEN, FOR ... NEXT, WITH ... END WITH), you close them out in the reverse order you opened them.
So the last one started is the first one to close.

Thanks, I was thinking completely the opposite. Some instinct I have?? LOL

 

[Fluff]

Something I used to do, when I was starting out, was to close the block as soon as I created it.
So you'd get this

Sub SheetNamesAsColumns()
 
   Dim ws As Worksheet
   Dim x As Integer
   
   x = 1
   For Each ws In Worksheets
   Next ws
    
End Sub

Followed by

Sub SheetNamesAsColumns()
 
   Dim ws As Worksheet
   Dim x As Integer
   
   x = 1
   For Each ws In Worksheets
      If ws.Name Like "**-**-**" Then
      End If
   Next ws
    
End Sub

Etc

 

[Jmoz092]

That's helpful Fluff, thank you.

 

[Roderick_E]

just wait until you do multiple loops and endifs

for x = 1 to 100
if x = 3 then
'do something
endif
for y = 2 to 200
if y = 150 then
'do something
endif
next y
next x