Looping through directory

[DHogg]

I have about 20 sheets in a folder on my hard drive, each containing one sheet tab.

I want to write a macro which loops through the directory these are saved in, and copys all of these so 1 workbook with 20 tabs. Is this possible with VBA?

 

[par60056]

This is a pretty simple task.

I put something together quickly. I think it will do what you want or be pretty close.

Sub mergeBooks()
Dim newBook As String
Dim thePath As String
Dim theFile As String
Dim theBook As String


Workbooks.Add
newBook = ActiveWorkbook.Name


thePath = InputBox("Enter source path", "Source Folder", ActiveWorkbook.Path)
If thePath <> "" Then
    ' Set the file specification to match the names of the files to merge
    theFile = Dir(thePath & "\*.xls*")
    While theFile <> ""
        Workbooks.Open thePath & "\" & theFile
        theBook = ActiveWorkbook.Name
        ActiveSheet.Copy after:=Workbooks(newBook).Sheets(Workbooks(newBook).Sheets.Count)
        Workbooks(theBook).Close False
        
        theFile = Dir
    Wend
End If
End Sub