I am trying to automatically update multiple pivot tables from one fixed button at the begining of the work book.
I have been able to do it with this code for normal pivot tables. But the pivot tables at work are directly linked to an OLAP query, I have a suspicion that they do not have the same PivotFields.
Do you know how I can assign a pivot field to the OLAP query Pivot table, Is there a way to look up the properties of the OLAP pivot table and then assin the "pviotfield" = to the correct property. ???????????????????
Sub UpDatePivot(Worksht As String, TableName As String)
' CHANGES THE FILTER PIVOT TABLE
Dim pvtTable As PivotTable
Dim pvtField As PivotField
Dim pvtItem As PivotItem
Dim filterName As String
Set pvtTable = Worksheets(Worksht).PivotTables(TableName)
Set pvtField = pvtTable.PivotFields("Month") <--------- Unable to get the PivotFields Property Of the Pivot Table Class
filterName = Worksheets("UpdateingInstructions").Range("P10")
For Each pvtItem In pvtField.PivotItems
If pvtItem.Value = filterName Then
pvtField.CurrentPage = filterName
Exit For
End If
Next pvtItem
End Sub
Sub UpdateAll()
Call UpDatePivot("Top15PL", "15PolymerSales")
Call UpDatePivot("Top15PL", "15PolymerRM")
Call UpDatePivot("Top15PL", "15PolymerCust")
Call UpDatePivot("Top15PL", "15IndustrialSales")
Call UpDatePivot("Top15PL", "15IndustrialRM")
Call UpDatePivot("Top15PL", "15IndustrialCust")
Call UpDatePivot("Top15PL", "15ConsumerSales")
Call UpDatePivot("Top15PL", "15ConsumerRM")
Call UpDatePivot("Top15PL", "15ConsumerCust")
End Sub
I have been able to do it with this code for normal pivot tables. But the pivot tables at work are directly linked to an OLAP query, I have a suspicion that they do not have the same PivotFields.
Do you know how I can assign a pivot field to the OLAP query Pivot table, Is there a way to look up the properties of the OLAP pivot table and then assin the "pviotfield" = to the correct property. ???????????????????
Sub UpDatePivot(Worksht As String, TableName As String)
' CHANGES THE FILTER PIVOT TABLE
Dim pvtTable As PivotTable
Dim pvtField As PivotField
Dim pvtItem As PivotItem
Dim filterName As String
Set pvtTable = Worksheets(Worksht).PivotTables(TableName)
Set pvtField = pvtTable.PivotFields("Month") <--------- Unable to get the PivotFields Property Of the Pivot Table Class
filterName = Worksheets("UpdateingInstructions").Range("P10")
For Each pvtItem In pvtField.PivotItems
If pvtItem.Value = filterName Then
pvtField.CurrentPage = filterName
Exit For
End If
Next pvtItem
End Sub
Sub UpdateAll()
Call UpDatePivot("Top15PL", "15PolymerSales")
Call UpDatePivot("Top15PL", "15PolymerRM")
Call UpDatePivot("Top15PL", "15PolymerCust")
Call UpDatePivot("Top15PL", "15IndustrialSales")
Call UpDatePivot("Top15PL", "15IndustrialRM")
Call UpDatePivot("Top15PL", "15IndustrialCust")
Call UpDatePivot("Top15PL", "15ConsumerSales")
Call UpDatePivot("Top15PL", "15ConsumerRM")
Call UpDatePivot("Top15PL", "15ConsumerCust")
End Sub