VBA Code:
Sub Reconcile()
Dim InputworkBook As Workbook
Dim ReportsToReconcile As Variant
ReportsToReconcile = Array("Report1", "Report2")
Dim ReconcilingReport As Variant
Dim ReportIndex As Integer
ReportIndex = 0
Set InputworkBook = Application.Workbooks.Open(Application.GetOpenFilename, ReadOnly:=True)
With InputworkBook
Dim PivotsToProcess As Variant
PivotsToProcess = Array("PivotTable2", "PivotTable3")
For Each ReconcilingReport In ReportsToReconcile
Dim DataSheet As Worksheet
Set DataSheet = .Worksheets(ReportsToReconcile(ReportIndex))
With DataSheet
.Parent.Parent.Visible = True
Dim DataPivot As PivotTable
Set DataPivot = .PivotTables(PivotsToProcess(ReportIndex))
With DataPivot
Dim PivotEntitiesToReconcile As Variant
PivotEntitiesToReconcile = Array(Array("Sum of STAT FX Book Value", "Sum of STAT FX Net Market Unrealized Valuation Gain/Loss", "Sum of STAT FX Net FX Unrealized Valuation GL"), Array("Sum of STAT FX Market Realized Gain + Sum of STAT FX Market Realized Loss", "Sum of STAT FX FX Realized Gain + Sum of STAT FX FX Realized Loss"))
Dim SheetEntitiesToReconcile As Variant
SheetEntitiesToReconcile = Array(Array("SH1", "SH2", "SH3"), Array("SHA", "SHB"))
Dim PivotEntity As Variant
Dim EntityIndex As Integer
EntityIndex = 0
For Each PivotEntity In PivotEntitiesToReconcile(ReportIndex)
If .GetPivotData(PivotEntity) = DataSheet.Cells.Find("Grand Total", after:=DataSheet.Columns(1).Find(SheetEntitiesToReconcile(ReportIndex)(EntityIndex)), searchorder:=xlByColumns).Offset(0, 2).Value Then MsgBox PivotEntity & " reconciles with " & SheetEntitiesToReconcile(ReportIndex)(i) Else MsgBox (EntityInPivot & " Breaks with " & SheetEntitiesToReconcile(ReportIndex)(EntityIndex)) & ". Break Value: " & .GetPivotData(PivotEntity) - DataSheet.Columns(1).Find("Grand Total", after:=DataSheet.Columns(1).Find(SheetEntitiesToReconcile(ReportIndex)(EntityIndex))).Offset(0, 2).Value
EntityIndex = EntityIndex + 1
Next PivotEntity
End With
End With
ReportIndex = ReportIndex + 1
Next ReconcilingReport
Call .Close(SaveChanges:=False)
End With
End SubThe problem is Here:
PivotEntitiesToReconcile = Array(Array("Sum of STAT FX Book Value", "Sum of STAT FX Net Market Unrealized Valuation Gain/Loss", "Sum of STAT FX Net FX Unrealized Valuation GL"), Array("Sum of STAT FX Market Realized Gain + Sum of STAT FX Market Realized Loss", "Sum of STAT FX FX Realized Gain + Sum of STAT FX FX Realized Loss"))
How to GetPivotData for two fields and Sum it up?
