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

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

And in the list of possible answer below the post, is this one...

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

Submitted in error - hit Tab, hit Enter ☺ retyping...

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

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

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

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

If it runs, then you're copying it in correctly.

Perhaps the data is contained on different sheets? The code works, I've tested it myself (Admittedly on Office 2007, but that shouldn't make a difference).

The procedure will only copy from the active sheet. If a file was saved with 'Sheet3' active and it's empty, nothing will be copied...

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

I'm sorry - you've basically just repeated your last post... have you looked at the source data? Is there anything there?

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

Sorry - but it imports with no problems here... at a loss what to suggest unless you can be more specific about what's happening at your end.

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

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

JCHULA
Welcome to the forum but please start your ownthread and refer to this one if required

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

I can run this code successfully but there is a bug.. it is only taking 50 rows from each file.. suggest what to do next

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

Suggest you start your own thread, explain the issue fully and upload a sample workbook that displays the issue.

If you think this thread can help clarify your issue you can include a link to it by copying the URL from the address bar of your browser and pasting into your message.