import data from other xls spreadsheets into one spreadsheet

**yarbrough40** · Jan 27 '10, 09:42 PM

put all the files containing the data to be merged into the same folder.
in that same folder create a seperate xls file and run this macro:

Enjoy!

Code:

Dim wkb As Workbook
    Dim wks As Worksheet
    Dim rngSource As Range
    Dim rngDestination As Range
    
    Dim myDir As String
    Dim fn As String
    
    myDir = ThisWorkbook.Path & "\"

    fn = Dir(myDir & "*.xls")
    
    Do While fn <> ""
        
        If fn <> ThisWorkbook.Name Then
            Set wkb = Workbooks.Open(myDir & fn)
            With wkb
                Set wks = .Sheets(1)
                Set rngSource = wks.Range("A1:V400")
                Set rngDestination = ThisWorkbook.Sheets(1).Range("a" & Rows.Count).End(xlUp)(2) _
                    .Resize(rngSource.Rows.Count, rngSource.Columns.Count)
                rngSource.Copy Destination:=rngDestination
                
                .Close False
            
            End With
        End If
        
        fn = Dir
    Loop
    
    Set rngSource = Nothing
    Set rngDestination = Nothing
    Set wks = Nothing
    Set wkb = Nothing

import data from other xls spreadsheets into one spreadsheet

import data from other xls spreadsheets into one spreadsheet

Comment