VBA to compile and collate Multiple excel workbooks and sheets data in one sheet in new excel file
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
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
- Store the workbook names into an array
- Open new workbook
- Loop through the array and open each workbook
- Copy the required range from required sheet
- Paste the range into new workbook
Here is the simple code to collate 3 workbooks into one new workbook:
How this works?
- 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)
- strSheetName = “Sheet1” ‘ This is the worksheet name in all workbooks: Storing the Worksheet names in a variable (strSheetName)
- strRng = “A1:A10” ‘ This is the range to be collate : Soring the Range address to be copied from each workbook into a variable (strRng)
- Set wbTarget = Workbooks.Add: Adding new workbook to paste all the required data
- Set shtTarget = wbTarget.Sheets(1) ‘Target worksheet in new workbook: Setting Target worksheet of the new workbook
- For i = 0 To 2…Next: Looping through the array of workbooks
- Open (ArrWorkbooks(i)): Opening each workbook specified in the array
- 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
- Close: Closing once pasted in the target worbook
You can refer the following link for the step by step instructions.
Click on the following links of the useful resources. These helps to learn and gain more knowledge.