Importing Multiple Workbooks that have multiple worksheets

[SloanRanger]

I’m trying to import a specific worksheet that appears in multiple (40) workbooks. I’m not fussed on how the worksheets are ordered in the new work book. The following code works but tried to pull all the worksheets and falls over when it finds hidden worksheets. As said I only want one worksheets copying.


Sub CombineWorkbooks()

Dim FName As String, FPath As String
Dim Sheet As Worksheet

Application.ScreenUpdating = False

FPath = Worksheets("Combine Sheets").Cells(2, 2).Value
FName = Dir(FPath & "\*.xls*")

Do While FName <> ""

Workbooks.Open Filename:=FPath & "\" & FName, ReadOnly:=True

For Each Sheet In ActiveWorkbook.Sheets

Sheet.Copy After:=ThisWorkbook.Sheets(1)

Next Sheet

Workbooks(FName).Close
FName = Dir()

Loop

Application.ScreenUpdating = True

End Sub

 

[GWteB]

One additional line to mumps' code will do that ...

Sub CombineWorkbooks_r2()
    Dim FName As String, FPath As String
    Dim desWB As Workbook
    Set desWB = ThisWorkbook
    Application.ScreenUpdating = False
    FPath = Worksheets("Combine Sheets").Cells(2, 2).Value
    FName = Dir(FPath & "\*.xls*")
    Do While FName <> ""
        Workbooks.Open Filename:=FPath & "\" & FName, ReadOnly:=True
        Sheets("Sheet1").Copy After:=desWB.Sheets(1)
        Workbooks(FName).Close False
        ActiveSheet.Name = Left(FName, (InStrRev(FName, ".")) - 1)
        FName = Dir()
    Loop
    Application.ScreenUpdating = True
End Sub

 

[SloanRanger]

Fantastic. To save me the agro as I’m trying to teach my self but is there a way to sort alphabetically

 

[Fazza]

google is great for that sort of thing

Sort Worksheets Alphanumerically by Name

Office VBA reference topic

docs.microsoft.com

 

[GWteB]

Fantastic.

Thanks for the feedback.

 

[mumps]

What is the password for each workbook?