OzGrid

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.

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:

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.

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

See also:

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery