How to Merge multiple Excel files into one file in separate sheets with source file name

How to merge multiple Excel files into one file in separate sheets with source file name

Requirement:

The user uses this code to merge multiple excel files (*.xlsx) to one master file in a different worksheets with the name sheet1, sheet2, sheet3,...

The user wants to put the destination sheet name with the source excel file name.

Code:

Public Sub test()
     '// Untested as written/edited  freehand, but the basic idea is there.
    Dim myFile As String, sh As Worksheet, myRange As Range
     
    Const myPath = "C:\Users\" ' to be modified
    Workbooks.Add 1 ' Add a new workbook
    myFile = Dir(myPath & "*.xlsx")
    
    Do While myFile <> ""
        Set sh = ActiveWorkbook.Sheets.Add()
        
        Workbooks.Open myPath & myFile
        Cells.Copy Destination:=sh.Range("A1")
         '       Set myRange = ActiveSheet.UsedRange
         '       Set myRange = myRange.Offset(1).Resize(myRange.Rows.Count - 1)
         '       myRange.Copy sh.Range("A65000").End(xlUp).Offset(1)
        ActiveSheet.Name = myFile
        Workbooks(myFile).Close False
        myFile = Dir
    Loop
End Sub

Solution:

Code:

Public Test_()
    Dim myFile As String, sh As Worksheet, myRange As Range
    Dim myPath As String
    
    myPath = InputBox("input the xlsx files path with a \ in the end  ")
        
    myFile = Dir(myPath & "*.xlsx")
     
     Dim sh_name As String




    Do While myFile <> ""
        Set sh = ActiveWorkbook.Sheets.Add()
        Workbooks.Open myPath & myFile
        
sh_name = Workbooks(myFile).ActiveSheet.Name
           
        Cells.Copy destination:=sh.Range("A1")
        
        sh.Name = sh_name
        
        Workbooks(myFile).Close False
        myFile = Dir
    Loop
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by patel and JCHULA.