How to compile and collate data from multiple workbooks and sheets into one new Excel file in VBA
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”
One thought on “How to compile and collate data from multiple workbooks and sheets into one new Excel file in VBA”