Posts by JCHULA

Re: Merge multiple Excel files into one file in separate sheets with source file name

Thanks for your help.
The code gives me a error, i think that was the file name extension, maybe to big...
I ´ve made a few modifications. I put the source worksheet name in the destination workbook and it works!

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

I use this code to merge multiple excel files (*.xlsx) to one master file in a different worksheets with the name sheet1, sheet2, sheet3,...
I want to put the destination sheet name with the source excel file name.
thks

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

Re: Merge multiple *.xls files into one *.xls file in separate sheets

great help...
using this code, how do i name every worksheet with the source workbook name instead sheet1, sheett2, ...
thanks

Quote from cytop;525897

Why dismiss it out of hand? Very little rework would get you...

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:\temp\" ' to be modified
    Workbooks.Add 1 ' Add a new workbook
    myFile = Dir(myPath & "*.xls")
 
   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)
        Workbooks(myFile).Close False
        myFile = Dir
    Loop
End Sub

Display More