List of all visible worksheets

[Gert-Jan]

Hi,

Can anyone help me with writing a macro that lists all the sheets of of a
workbook to a certain range?

I have this:

Sub test()
Dim NumSheets As Integer
Dim sht As Object
NumSheets = Sheets.Count
For j = 1 To NumSheets
Cells(j + 1, 8) = Sheets(j).Name
Next j
End Sub

But it writes ALL sheetnames, not only the visible.

Thanks in advance, Gert-Jan

 

[John]

Gert-Jan,

You're almost there. Just add a If statement to check if the respective
sheet is visible:

Sub test()
Dim NumSheets As Integer
Dim sht As Object
NumSheets = Sheets.Count
For j = 1 To NumSheets
If Sheets(j).Visible = True Then
Cells(j + 1, 8) = Sheets(j).Name
End If
Next j
End Sub

You could also add a separate row counter to prevent any gaps in the list:

Sub test()
Dim NumSheets As Integer
Dim sht As Object
Dim iRow As Integer
'Set the start row
iRow = 2
NumSheets = Sheets.Count
For j = 1 To NumSheets
If Sheets(j).Visible = True Then
Cells(iRow, 8) = Sheets(j).Name
iRow = iRow + 1
End If
Next j
End Sub

Hope that helps

Best regards

John

 

[Don Guillett]

Sub listvisiblesheets()
mc = 2
For Each ws In Worksheets
If ws.Visible = True Then
Cells(mc, 2) = ws.Name
mc = mc + 1
End If
Next
End Sub

 

[Gert-Jan]

Hi Don,

Thanks a lot, this works fine!

Gert-Jan

 

[Don Guillett]

Glad to help.

 

[John]

Me too!

John

Glad to help.

 

[Don Guillett]

The problem with yours is that it left blank rows for the hidden sheets

 

[John]

Hello Don,

Yes I know. That's why I added the second sub in my post.

Never mind, I can deal with the pain

Best regards

John

 

[Don Guillett]

I stand corrected. I guess OP and I didn't see it.