Consolidate Date from Multiple Excel Workbooks using VBA

[arijit]

Hello Everyone,

I am learning VBA in excel for the last few weeks with the help of different websites and video tutorials. In the meantime i was trying to consolidate data from multiple excel workbooks then adding up the value (like salary of "A,B & C") in the mater file using VBA in excel.

However, i am getting an error message i.e. "Run-time error '9': Subscript out of range in the below specified line:

Windows("D:\VBA & EXCEL\Consolidate.xlsm").Activate

I have created three different excel sheets in the below specified path of my PC naming a,b & c with extension xlsx.
D:\VBA & EXCEL\a.xlsx

The master file is also created in the above specified path naming Consolidate.xlsm.

Entire coding is specified below for your reference.
-------------------------------------------------------------------------------------------------------------------------------------------------------------------


Sub ConsolidateData()


Range("A1").Select
ActiveCell.Value = "Name"
Range("B1").Select
ActiveCell.Value = "Salary"
Range("A2").Select
ActiveCell.Value = "Sudipto"
Range("A3").Select
ActiveCell.Value = "Chayan"
Range("A4").Select
ActiveCell.Value = "Arunabha"


Range("B2").Select




Workbooks.Open Filename:="D:\VBA & EXCEL\a.xlsx"
Workbooks.Open Filename:="D:\VBA & EXCEL\b.xlsx"
Workbooks.Open Filename:="D:\VBA & EXCEL\c.xlsx"




Windows("D:\VBA & EXCEL\Consolidate.xlsm").Activate


Selection.Consolidate Sources:=Array(" 'D:\VBA & EXCEL\[a.xlsx]Sheet1'!R2C2:R4C2", " 'D:\VBA & EXCEL\[b.xlsx]Sheet1'!R2C2:R4C2", " 'D:\VBA & EXCEL\[c.xlsx]Sheet1'!R2C2:R4C2"), Function:=xlSum




Windows("D:\VBA & EXCEL\a.xls").Activate
ActivateWorkbook.Close
Windows("D:\VBA & EXCEL\b.xls").Activate
ActivateWorkbook.Close
Windows("D:\VBA & EXCEL\c.xls").Activate
ActivateWorkbook.Close


End Sub




I would be very grateful if someone assist me to resolve the issue in the same procedure which i am trying now.