Importing worksheets from files stored in a folder

[alekos7]

Hello!

I'm trying to import all worksheets from all files stored a folder named "Path Folder" into an active workbook.

Example
I have 3 workbooks named Project 1, Project 2 and Project 3 in the folder named "Path Folder" and each workbook contains two sheets.
I need to pull all 6 sheets into my active workbook named "Master".

I've tried a couple different things and they aren't working. Thanks for your help!

 

[John_w]

What type of files are they? .xlsx, .xls, etc? This code imports all worksheets from all the .xlsx files in a folder:

Public Sub Import_All_Worksheets_From_All_Workbooks_In_Folder()

    Dim folderPath As String, fileName As String
    Dim destinationWorkbook As Workbook, sourceWorkbook As Workbook
    Dim ws As Worksheet

    Set destinationWorkbook = ActiveWorkbook
    
    folderPath = "C:\Path Folder\"   'CHANGE THIS TO SUIT
        
    folderPath = Trim(folderPath)
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
    
    Application.ScreenUpdating = False

    fileName = Dir(folderPath & "*.xlsx")
    While fileName <> vbNullString
        Set sourceWorkbook = Workbooks.Open(folderPath & fileName)
        For Each ws In sourceWorkbook.Worksheets
            With destinationWorkbook
                ws.Copy After:=.Worksheets(.Worksheets.Count)
                '.Worksheets(.Worksheets.Count).Name = .Worksheets(.Worksheets.Count).Name & " " & .Worksheets.Count
            End With
        Next
        sourceWorkbook.Close savechanges:=False
        fileName = Dir
    Wend
    
    Application.ScreenUpdating = True
    
End Sub