arijit
06-24-2017, 11:32 PM
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.
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.