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