VBA to compile and collate Multiple excel workbooks and sheets data in one sheet in new excel file

VBA to compile and collate Multiple excel workbooks and sheets data in one sheet in new excel file

Question:

Hi… I’m looking for comment for below scenario.

1.  I have 3 excel file named ABC1, ABC2 and ABC3.
2.  I want to copy selected range of rows and column (it’s same for all the 3 excel sheet), in to new excel file

4.  It should paste all the 3 excel file data in to new excel file within one sheet one below other data

basically i want compile all the 3 excel sheet data in one sheet in new excel file

Can you pls help and guide on this.  Thanks

Answer:

Hi,
Here is simple approch to compile and collate the multiple workbooks into one workbook using VBA:

VBA to compile and collate multiple workbooks into one workbook

  1. Store the workbook names into an array
  2. Open new workbook
  3. Loop through the array and open each workbook
  4. Copy the required range from required sheet
  5. Paste the range into new workbook

Here is the simple code to collate 3 workbooks into one new workbook:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub sbCompileAndCollateMultipleWorkbooks()
'Requirements
ArrWorkbooks = Array("C:TempFilesFolderFile 1.xls", "C:TempFilesFolderFile 2.xls", "C:TempFilesFolderFile 3.xls") 'workbooks
strSheetName = "Sheet1" ' This is the worksheet name in all workbooks
strRng = "A1:A10" ' This is the range to be collate
'Open New Workbook
Set wbTarget = Workbooks.Add
Set shtTarget = wbTarget.Sheets(1) 'Target worksheet in new workbook
For i = 0 To 2
Workbooks.Open (ArrWorkbooks(i))
Sheets(strSheetName).Range(strRng).Copy Destination:=shtTarget.Range("A" & (i) * Worksheets(strSheetName).Range(strRng).Rows.Count + 1)
ActiveWorkbook.Close
Next
End Sub

How this works?

  1. ArrWorkbooks = Array(“C:TempFilesFolderFile 1.xls”, “C:TempFilesFolderFile 2.xls”, “C:TempFilesFolderFile 3.xls”) ‘workbooks : We are storing the source workbook names into an array (ArrWorkbooks)
  2. strSheetName = “Sheet1” ‘ This is the worksheet name in all workbooks: Storing the Worksheet names in a variable (strSheetName)
  3. strRng = “A1:A10” ‘ This is the range to be collate : Soring the Range address to be copied from each workbook into a variable (strRng)
  4. Set wbTarget = Workbooks.Add: Adding new workbook to paste all the required data
  5. Set shtTarget = wbTarget.Sheets(1) ‘Target worksheet in new workbook: Setting Target worksheet of the new workbook
  6. For i = 0 To 2…Next: Looping through the array of workbooks
  7. Open (ArrWorkbooks(i)): Opening each workbook specified in the array
  8. Sheets(strSheetName).Range(strRng).Copy Destination:=shtTarget.Range(“A” & (i) * Worksheets(strSheetName).Range(strRng).Rows.Count + 1): Copying the required range into worksheet in the target workbook
  9. Close: Closing once pasted in the target worbook

 

Leave a Comment

Your email address will not be published. Required fields are marked *