VBA list sheetnames but exclude hidden sheets

[EMcK01]

Hi,

Looking for some assistance. I have the following code that lists all worksheets from an open workbook.

Sub ListWS()

Dim FolderPath1 As String


Dim data1 As Worksheet


Set data1 = Worksheets("DATA")


FolderPath1 = data1.Cells(5, 3)
FileName1 = data1.Cells(4, 3)
    


FileName2 = Dir(FolderPath1 & "*.xlsx")


With Sheets("WORKING").Activate
End With


    Set wbk = Workbooks(FileName2)
    
    Set wbk = ActiveWorkbook


            For i = 3 To Workbooks(FileName2).Sheets.Count
            Cells(i, 2) = Workbooks(FileName2).Sheets(i).Name
            Next i


End Sub

This works fine however I would like to ignore any hidden worksheet which this currently isn't doing. I had tried to add

If ws.Visible = True Then
For i = 3 To Workbooks(FileName2).Sheets.Count

But I get a Run-time error. Any idea where I'm going wrong?

Thanks,
EMcK

 

[DanteAmor]

Try this

Sub ListWS()


    Dim FolderPath1 As String
    Dim data1 As Worksheet
    
    Set data1 = Worksheets("DATA")
    
    FolderPath1 = data1.Cells(5, 3)
    FileName1 = data1.Cells(4, 3)
    FileName2 = Dir(FolderPath1 & "*.xlsx")
    
    With Sheets("WORKING").Activate
    End With
    
    Set wbk = Workbooks(FileName2)
    Set wbk = ActiveWorkbook


[COLOR=#0000ff]    Dim n As Long[/COLOR]
[COLOR=#0000ff]    n = 3[/COLOR]
    For i = 3 To Workbooks(FileName2).Sheets.Count
[COLOR=#0000ff]        If Workbooks(FileName2).Sheets(i).Visible = True Then[/COLOR]
            Cells([COLOR=#0000ff]n[/COLOR], 2) = Workbooks(FileName2).Sheets(i).Name
[COLOR=#0000ff]            n = n + 1[/COLOR]
        End If
    Next i
End Sub

 

[EMcK01]

Perfect, thank you.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.