Copying different excel files in a folder to one excel file as sheets

Try this:

Sub CollateReportFromFiles()
'Open all .XLS in specific folder (2007 compatible)
Dim strFileName As String, strPath As String, MyVal As String
Dim wbkOld As Workbook, wbkNew As Workbook, ws As Worksheet
    
Application.EnableEvents = False
Application.DisplayAlerts = False
    
Set wbkNew = ThisWorkbook
strPath = "C:\Report\"
strFileName = Dir(strPath & "*.xls")
wbkNew.Activate
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Temp"

    For Each ws In Worksheets
        If ws.Name <> "Temp" Then ws.Delete
    Next ws

ActiveSheet.Name = "Final"

    Do While Len(strFileName) > 0
        Set wbkOld = Workbooks.Open(strPath & strFileName)
        MyVal = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)
        wbkOld.Sheets(MyVal & ".xdo").Activate
        'Copy After:=wbkNew.Sheets(Sheets.Count)
        ActiveSheet.Copy After:=wbkNew.Sheets(wbkNew.Sheets.Count)
        strFileName = Dir
        wbkOld.Close False
    Loop
    
wbkNew.Sheets("Final").Delete
Application.DisplayAlerts = False
Application.EnableEvents = True
End Sub

This macro can be run and it will update an existing report, too, clearing all the old entries and creating an entirely new compilation report from the noted folder.

Thanks JBeaucaire for your reply,I tried with the macro what you sent but getting error...out of range run-time script 9 like that....

Please add the following line ABOVE my macros title line:

Option Explicit

Then, while still in the VBA editor, click on Debug > Compile VBAProject. See if the debugger points out any visible problems with your edits in advance.

Then run the macro again and when the error pops up (if it does again), be sure to select DEBUG so that it will open the editor and highlight the line of code it is failing on.

One simple idea, any chance you forgot the final "\" when editing the strPath?

I ran the module I wrote you multiple times and it seems to work fine, but my sheets are EXACTLY the way you described. Any chance your original specifications were off somehow? Is the sheet needed inside each workbook REALLY called by the exact same name as the workbook plus .xdo?

You could post a sanitized version of one of the import sheets so I can look at the layout.


BTW, your macro is pretty much the same exact approach mine uses, you've just got different variable names. It's the same critter.

Can you zip the file(s) and not rar files.

The error on the line you've noted means the sheetname does not exist in the workbook that opened. Is that the letter "o" or the number "0" on there? .xdo and xd0 are different things entirely. Verify your sheetnames are what you think they are, the macro works because my sample sheets all have "A.xdo", "B.xdo" sheets in them...

As per our Skype conversation, I have removed the "xdo" activation step since each workbook being opened only has one sheet in it, this is an unnecessary step.

Sub CollateReportFromFiles()
'Open all .XLS in specific folder (2007 compatible)
Dim strFileName As String, strPath As String, MyVal As String
Dim wbkOld As Workbook, wbkNew As Workbook, ws As Worksheet

Application.EnableEvents = False
Application.DisplayAlerts = False

Set wbkNew = ThisWorkbook
strPath = "C:\Report\"
strFileName = Dir(strPath & "*.xls")
wbkNew.Activate
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Temp"

    For Each ws In Worksheets
        If ws.Name <> "Temp" Then ws.Delete
    Next ws

    Do While Len(strFileName) > 0
        Set wbkOld = Workbooks.Open(strPath & strFileName)
        MyVal = Range("D4")
        ActiveSheet.Name = "Extra ESN-" & MyVal & ".xdo"
        ActiveSheet.Copy After:=wbkNew.Sheets(wbkNew.Sheets.Count)
        strFileName = Dir
        wbkOld.Close False
    Loop
    
wbkNew.Sheets("Temp").Delete
Application.DisplayAlerts = False
Application.EnableEvents = True
End Sub

I highlighted the updated lines...