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