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 please help and guide on this.  Thanks

Answer:

Hi,
Here is simple approach 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:

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

Instructions to Run VBA Macro Code or Procedure:

You can refer the following link for the step by step instructions.

Instructions to run VBA Macro Code

Other Useful Resources:

Click on the following links of the useful resources. These helps to learn and gain more knowledge.

VBA Tutorial VBA Functions List VBA Arrays VBA Text Files VBA Tables

VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers Blog