Worksheets(1) Ignore hidden worksheet

Question

I've been working my way through a macro that will merge the first visible worksheet from an entire folder of selected workbooks. With plenty of help, I have the code working for the first worksheet in each workbook, but it is picking up hidden worksheets and I only want the first visible sheet. Here is the code so far:

Option Explicit

Function GetFolder(strPath As String) As String
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .InitialFileName = strPath
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Function


Sub CombineFiles()

    Dim Path            As String
    Dim FileName        As String
    Dim Wkb             As Workbook
    Dim WS              As Worksheet

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Path = GetFolder("Navigate to folder")
    FileName = Dir(Path & "\*.xl??", vbNormal)
    Do Until FileName = ""
        Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName, ReadOnly:=True, UpdateLinks:=False)
            Wkb.Worksheets(1).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
            Wkb.Close False
            FileName = Dir()
    Loop
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub

Pᴇʜ Pᴇʜ · Accepted Answer · 2018-03-15T15:55:05

you can loop until a visible worksheet is found

Dim i As Long
i = 1
Do While Wkb.Worksheets(i).Visible = False
    If i >= Wkb.Worksheets.Count Then
        MsgBox "No visible sheet found"
        Exit Do
    End If
    i = i + 1
Loop
Debug.Print Worksheets(i).Name 'first visible sheet

Worksheets(1) Ignore hidden worksheet

1 Answers