Consolidate multiple workbook data into master workbook data - Reg.

[bala04msw]

Dear Forum Experts,

Kindly guide me for the following requirements.

Requirements: - I want to consolidate data from 100 workbooks into master workbook along with headers and column indicators.

Example: each workbooks contains the structure of table

Particulars Qty-1 Qty-2 Qty-3

A company 10 20 15
B company 05 15 20
C company 08 10 15

Scenario:

Each excel Workbook contains: 8 worksheets (MPR_Sec1, MPR_Sec2, MPR_Sec3......) and likewise 100 workbooks
Database folder pathway: "C:\Users\MAHI\Desktop\Others\MPR\National_MPR_Consolidation\DataBase_MPR\KL-MPR.xlsx"
Master consolidation workbook has been placed: "C:\Users\MAHI\Desktop\Others\MPR\National_MPR_Consolidation"
Master Data Sheet (Consolidation) also contains 8 worksheets,

I want to consolidate 100 workbooks MPR_Sec1 data in Master workboob MPR_Sec1 sheet. I have used the following code and Run-time error -9 Subscript out of range error popups in "Windows("National-MPR-Consolidation").Activate" consolidation data.

Sub consolidateData()
Range("B2").Select
ActiveCell.Value = "Indicator No."
Range("C2").Select
ActiveCell.Value = "S.No"
Range("D2").Select
ActiveCell.Value = "Indicator Name"
Range("E2").Select
ActiveCell.Value = "Variable Type"
Range("F2").Select
ActiveCell.Value = "Source of Data"
Range("B3").Select
ActiveCell.Value = "A"
Range("C3").Select
ActiveCell.Value = "S.No"
Range("D3").Select
ActiveCell.Value = "Site Specific Information"
Range("G3").Select
ActiveCell.Value = "April"
Range("B4").Select
ActiveCell.Value = "A(i)"
Range("C4").Select
ActiveCell.Value = "S.No"
Range("D4").Select
ActiveCell.Value = "Govt and PPP"
Range("G4").Select
ActiveCell.Value = "SA-ICTC"
Range("H4").Select
ActiveCell.Value = "F-ICTC"
Range("I4").Select
ActiveCell.Value = "PPP-FICTC"
Range("J4").Select
ActiveCell.Value = "3 Test PPP"
Range("K4").Select
ActiveCell.Value = "Total"
Range("C5").Select
ActiveCell.Value = "1"
Range("D5").Select
ActiveCell.Value = "No. of facilities in the program till last month"
Range("G5").Select
Workbooks.Open Filename:="C:\Users\MAHI\Desktop\Others\MPR\National_MPR_Consolidation\DataBase_MPR\KL-MPR.xlsx"
Workbooks.Open Filename:="C:\Users\MAHI\Desktop\Others\MPR\National_MPR_Consolidation\DataBase_MPR\PY-MPR.xlsx"
Workbooks.Open Filename:="C:\Users\MAHI\Desktop\Others\MPR\National_MPR_Consolidation\DataBase_MPR\TN-MPR.xlsx"
Windows("National-MPR-Consolidation").Activate
Selection.Consolidate Sources:=Array( _
"'C:\Users\MAHI\Desktop\Others\MPR\National_MPR_Consolidation\DataBase_MPR\[KL-MPR.xlsx]MPR_Sec1'!R5C7:R5C7", _
"'C:\Users\MAHI\Desktop\Others\MPR\National_MPR_Consolidation\DataBase_MPR\[PY-MPR.xlsx]MPR_Sec1'!R5C7:R5C7", _
"'C:\Users\MAHI\Desktop\Others\MPR\National_MPR_Consolidation\DataBase_MPR\[TN-MPR.xlsx]MPR_Sec1'!R5C7:R5C7"), Function:=xlSum
Windows("KL-MPR.xlsx").Activate
ActiveWorkbook.Close
Windows("PY-MPR.xlsx").Activate
ActiveWorkbook.Close
Windows("TN-MPR.xlsx").Activate
ActiveWorkbook.Close
End Sub

Kindly do the needful.

Regards

[Keebellah]

Hi, I just did a quick reading of your code, but you cannot activate a closed workbook.

[bala04msw]

Dear Keebellah,

Based on your suggestion, i have tried out and remove workbook close function and again facing same issue. Kindly do the needful.

Regards

[Keebellah]

Try this
Don't know if it works
Else you will have to step though the code line by line and see where it stops. Maybe an incorrect reference but you have the data to test it

Please Login or Register  to view this content.

[bala04msw]

Great Keebellah,

Works perfect and thanks a lot. What is the logic?

[Keebellah]

The source workbook is the one you need to return the focus to.
My experience has been that the Windows("bla bla xlsx") activate doens't work since Office 2010
This is because Excel can open a workbook in a different session which is no longer recognized after you open one or more extra workbooks, which I am assuming happened here.

I always store the ActiveWorkbook in a variable, in this case myWorkBook so that Excel 'knows' what it's looking for.

I hope I was able to explain my thoughts, it's easier to do than explain