Open multiple files in a folder with vba

[HebertsYES]

Hi i need a macro to do this:
open all of the excel files in a selected folder, put each one on a separate sheet in one workbook. Is this possible? thanks so much

 

[Momentman]

Is that to say that each file has only one sheet?

 

[HebertsYES]

Yes just one sheet in each file

 

[Momentman]

I was gonna write the code from scratch but i was sure someone had already done that

I got one here by Norie(excel Legend) http://www.mrexcel.com/forum/excel-...heet-single-workbook-multiple-worksheets.html

Sub Merge2MultiSheets()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsSrc As Worksheet
Dim MyPath As String
Dim strFilename As String
    
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    MyPath = "C:\Path" ' change to suit
    Set wbDst = Workbooks.Add(xlWBATWorksheet)
    strFilename = Dir(MyPath & "\*.xlsx", vbNormal)
    
    If Len(strFilename) = 0 Then Exit Sub
    
    Do Until strFilename = ""
        
            Set wbSrc = Workbooks.Open(Filename:=MyPath & "\" & strFilename)
            
            Set wsSrc = wbSrc.Worksheets(1)
            
            wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)
            
            wbSrc.Close False
        
        strFilename = Dir()
        
    Loop
    wbDst.Worksheets(1).Delete
    
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
End Sub

The strings in Red/Bold are what you should pay attention to and alter if necessary

 

[HebertsYES]

Thank you much. I will try it when I get an opportunity