In this article we will see how to copy data from multiple Excel workbooks and paste it to a new Excel Workbook.

Consider you have three Excel workbooks with data in a single sheet with the same name. The range from which data is to be copied is also same across the three workbooks.

The data from all these workbooks is to be copied to a new workbook. For this, we will be using the code below. Use the comments in the code to follow through.

Sub collateData()
    Dim SourceArray
    Dim SheetName As String, SourceRange As String
    Dim TargetWorkbook As Workbook, sourceFile As Workbook
    Dim TargetSheet As Worksheet
    Dim i As Integer
    Dim LastRow As Long
    
    'Complete path of all the files to copy data from should be added here
    SourceArray = Array("E:\Source1.xlsx", "E:\Source2.xlsx", "E:\Source3.xlsx")
    
    'Name of the sheet in the source files
    SheetName = "Sheet1"
    
    'Source range
    SourceRange = "A2:D8"
    
    'Open a new Workbook
    Set TargetWorkbook = Workbooks.Add
    
    'Set the sheet in the target workbook to paste data
    Set TargetSheet = TargetWorkbook.Sheets("Sheet1")
    
    'For each of the files in the array
    For i = 0 To UBound(SourceArray)
        'Open the source workbook
        Set sourceFile = Workbooks.Open(SourceArray(i))
        
        'Get Last Row of target sheet
        LastRow = TargetSheet.Cells(TargetSheet.Rows.Count, 1).End(xlUp).Row
    
        With sourceFile
            'Copy the data to the end of the target sheet
            .Sheets(SheetName).Range(SourceRange).Copy Destination:=TargetSheet.Range("A" & LastRow + 1)
            
            'Close the source workbook
            .Close
        End With
            
    Next
    
    'Save the target workbook
    TargetWorkbook.SaveAs "E:\Target.xlsx"
    
End Sub

The output will look like this:

Instead of pasting the data to a new file, you can use an existing Excel workbook to paste the data. For that, replace the code

    Set TargetWorkbook = Workbooks.Add
    TargetWorkbook.SaveAs "E:\Target.xlsx"

with

    Set TargetWorkbook = Workbooks.Open("E:\Target.xlsx")
    TargetWorkbook.Save

Here, we have assumed that the Sheet Name and data range of the source is fixed. If that differs from file to file you can modify the code as below:

Sub collateDataDynamic()
    Dim SourceArray, SheetNames
    Dim SourceRange As String
    Dim TargetWorkbook As Workbook, sourceFile As Workbook
    Dim TargetSheet As Worksheet, SourceSheet As Worksheet
    Dim i As Integer
    Dim LastRow As Long, lastSourceRow As Long
    
    'Complete path of all the files to copy data from should be added here
    SourceArray = Array("E:\Source1.xlsx", "E:\Source2.xlsx", "E:\Source3.xlsx")
    
    'Name of the sheet in the source files
    'Sheet Names should correspond to the file names
    SheetNames = Array("Jan", "Feb", "Mar")
    
    'Source range can also be specified in an array
    'Here I am assuming that the column span is fixed
    'And data starts from cell A2
    'The end row will be the last row in the source file
    SourceRange = "A2:D"
    
    'Open the target Workbook
    Set TargetWorkbook = Workbooks.Open("E:\Target.xlsx")
    
    'Set the sheet in the target workbook to paste data
    Set TargetSheet = TargetWorkbook.Sheets("Sheet1")
    
    'For each of the files in the array
    For i = 0 To UBound(SourceArray)
        'Open the source workbook
        Set sourceFile = Workbooks.Open(SourceArray(i))
        
        'Get Last Row of target sheet
        LastRow = TargetSheet.Cells(TargetSheet.Rows.Count, 1).End(xlUp).Row
    
        'Get the source sheet
        Set SourceSheet = sourceFile.Sheets(SheetNames(i))
        
        With SourceSheet
            'Get the last row of the source sheet
            lastSourceRow = .Cells(.Rows.Count, 1).End(xlUp).Row
            
            'Copy the data to the end of the target sheet
            .Range(SourceRange & lastSourceRow).Copy Destination:=TargetSheet.Range("A" & LastRow + 1)
            
        End With
        
        'Close the source workbook
        sourceFile.Close
            
    Next
    
    'Save the target workbook
    TargetWorkbook.Save
    
End Sub

The output will be same as before. But in this case, the sheet name and number of rows need not be same across all the source files.

For further information on copy pasting data, refer to “Why is the VBA Range.copy Method Useful?”
For further information on working with multiple workbooks, refer to “VBA Excel Working with Multiple Workbooks”