VBA Script to Copy Multiple Excel Files into one excel file, putting them into Mult tabs

[pmlazalde]

I need your help! I have multiple excel files in one directory that I need to copy into one master excel file and copy each incoming file into a separate tab (and putting file name on tab) in the master excel file. I do have the below VBA macro but it really doesn't work correctly. It seems to randomly make duplicate copies of some files and doesn't put name on the tab correctly on some files as well. Its not a reliable script. Can someone please help! I would appreciate it very much! Thanks in advance for looking at this for me. Phil


Sub Copyfiles()
Path = "C:\Users\Phil\Desktop\Matrices - Access\"
Filename = Dir(Path & "*.xls")


Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy after:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub

 

[John_w]

Try this:

Sub CopyFiles()

    Dim path As String, fileName As String
    Dim ws As Worksheet
    
    path = "C:\Users\Phil\Desktop\Matrices - Access\"
    
    fileName = Dir(path & "*.xls")
    Do While fileName <> ""
        Workbooks.Open fileName:=path & fileName, ReadOnly:=True
        For Each ws In ActiveWorkbook.Sheets
            ws.Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
            ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name = fileName & " " & ws.Name
        Next
        Workbooks(fileName).Close
        fileName = Dir()
    Loop
    
End Sub

 

[pmlazalde]

Hi John,

That did it!! Thanks so much! I truly appreciate your time in doing this for me. Thanks again very much!!!

 

[sur]

Good One,

Path of the file keeps changing regularly, how can we make to path to selected one ?