Macro to Merge different Excel files into one

I have a standard Consolidation macro to do this. Edit the lines marked in red to your needs and it should do what you want.

Option Explicit

Sub Consolidate()
'Open all Excel files in a specific folder and import data as separate sheets
'Renames imported sheets to match workbook name of each source file
'JBeaucaire (7/6/2009)     (2007 compatible)
Dim strFileName As String, strPath As String
Dim wbkOld As Workbook, wbkNew As Workbook, ws As Worksheet

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

Set wbkNew = ThisWorkbook
strPath = "C:\Documents and Settings\Jerry\Files\"    'Edit to your path, be sure to end with \
If Left(strPath, 1) <> "\" Then strPath = strPath & "\"

strFileName = Dir(strPath & "*.xl*")
wbkNew.Activate

'Clear existing files (optional, remove this section if appending is desired)
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Temp"
    For Each ws In Worksheets
        If ws.Name <> "Temp" Then ws.Delete
    Next ws
    
'Import first active sheet from found file
    Do While Len(strFileName) > 0
        Set wbkOld = Workbooks.Open(strPath & strFileName)
        ActiveSheet.Name = Left(strFileName, Len(strFileName) - 4)
        ActiveSheet.Copy After:=wbkNew.Sheets(wbkNew.Sheets.Count)
        strFileName = Dir
        wbkOld.Close False
    Loop
    
wbkNew.Sheets("Temp").Delete
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Hi Forum,

Thanks so much for the code. It is working. Currently in the macro we have given a path to the files, so anyone using the macro has to create the same folders etc. I wanted to change it like once we run the macro it should ask for the path, like it should only point to dir and then we can select the path where the files are saved.

Also I want to clear the color (unfill) on the final workbook.

Is that possible ?

Thanks,
Abhilash

Of course, but are you 100% positive there will be no duplication of tab names between any two sheets? Absolutely 100%? It really makes me nervous opening 60 "unknown" files and moving in the only sheet in each and not using a sheet-naming method we know in advance won't result in duplicate sheet names.

Please advise.

I've highlighted in blue in my post above the line of code you could remove to have the sheets NOT be renamed prior to importing.

NOTE: Please don't use the QUOTE button, it duplicates my post completely inside of yours for no reason. Just use the QUICK REPLY box below. Use the QUOTE if you do want to quote something specific I've said, but only leave in that portion, remove the rest.

This makes thread much less cumbersome to read.

Abhilash B,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.