VBA Code to Update Pivot Table Filters

[torijm]

I am trying to update multiple pivot table fields based on values given in a reference tab.

The pivot tables I am using are linked to an OLAP query and I can not seem to find a way to get the data to pull from cells in another tab.

Do you know how I can update the cells in the reference tab and use a button that updates all of the pivot tables?
Also, I could update the first pivot table in the tab and have the rest reset to those updated fields. I do not know how to do this either.

Here is the code that I have from recording a macro that updates one pivot table to the right settings:

Sheets("Cube").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[Transaction Date].[Transaction Date].[Year attribute 1]").VisibleItemsList = _
Array("")
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[Transaction Date].[Transaction Date].[Quarter attribute]").VisibleItemsList _
= Array("")
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[Transaction Date].[Transaction Date].[Month attribute 1]").VisibleItemsList _
= Array("")
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[Transaction Date].[Transaction Date].[Day attribute 1]").VisibleItemsList = _
Array( _
"[Transaction Date].[Transaction Date].[Year attribute 1].&[2012].[Quarter 2].[June].[1]" _
, _
"[Transaction Date].[Transaction Date].[Year attribute 1].&[2012].[Quarter 2].[June].[2]" _
, _
"[Transaction Date].[Transaction Date].[Year attribute 1].&[2012].[Quarter 2].[June].[3]" _
, _
"[Transaction Date].[Transaction Date].[Year attribute 1].&[2012].[Quarter 2].[June].[4]" _
)

I would like to be able to change the "2012", "Quarter 2", "June", and "1,2,3,4" based on either cells in a reference tab or based on the first pivot table in the "Cube" tab.


This is the way I have tried to pull values from the "reference" tab, but it gives me and error and doesnt register that "Current_Month" is equal to a value.

Sub Update_Pivot_Table_All()
'
' Update_Pivot_Table_All Macro
Dim Current_Month As String
Current_Month = Sheets("Reference").Range("D2").Text
Dim Current_Year As Integer
Current_Year = Sheets("Reference").Range("B4").Value
Dim Current_Quarter As String
Current_Quarter = Sheets("Reference").Range("B3").Value
Dim Current_Days As Integer
Current_Days = Sheets("Reference").Range("E2").Value


Sheets("Cube").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[Transaction Date].[Transaction Date].[Year attribute 1]").VisibleItemsList = _
Array("")
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[Transaction Date].[Transaction Date].[Quarter attribute]").VisibleItemsList _
= Array("")
ActiveSheet.PivotTables("PivotTable1").PivotFieds( _
"[Transaction Date].[Transaction Date].[Month attribute 1]").VisibleItemsList _
= Array("")
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[Transaction Date].[Transaction Date].[Day attribute 1]").VisibleItemsList = _
Array( _
"[Transaction Date].[Transaction Date].[Year attribute 1].&["Current_Year"].["Current_Quarter"].["Current_Month"].["Current_Days"]" _
, _
"[Transaction Date].[Transaction Date].[Year attribute 1].&[2012].[Quarter 2].[June].[2]" _
, _
"[Transaction Date].[Transaction Date].[Year attribute 1].&[2012].[Quarter 2].[June].[3]" _
, _
"[Transaction Date].[Transaction Date].[Year attribute 1].&[2012].[Quarter 2].[June].[4]" _
)
End Sub

I am grateful for any and all help. Thank you all in advance.

 

[Etv5002]

Is it possible for your pivot table to show all the relevant combinations at the same time?

Bc if so it would be easier for adjustments and troubleshooting to have a complete table then on a separate page we'll call a the "Report" Page that is formatted with Getpivot formulas based on criteria in another cell.

I have a form of this set up My macro pulls the data, pivots it with all the information that anyone should be looking for even calculated columns, then labels are updated on a separate page where the get pivot formulas run off a dropdown list at the top.

 

[torijm]

I am not sure what you mean by "show all relevant combinations at the same time."

The pivot tables are from an OLAP query, so there isnt a table that the data is stored in, that I know of.

I am not a VBA expert by any means, so if I am misunderstanding things I apologize. I will try my best to explain.

 

[torijm]

This question has also been posted in the following forum:
http://social.msdn.microsoft.com/Forums/eu/isvvba/thread/8fbf1f65-0161-4d67-bc47-9917873e2346

 

[BarneyStinson]

If you use "Worksheets("Reference").Range("E2").Value" It should work.

I update a current pivot with the following:
Sheets("Pivot-IM").PivotTables("IM-Day").PivotFields("Months").ClearAllFilters
Sheets("Pivot-IM").PivotTables("IM-Day").PivotFields("Months").CurrentPage _
= Worksheets("Daily").Range("B2").Value

 

[torijm]

This works if I am only choosing one value out of the month, but how would I use it if I wanted it to pick up the values in the range E2:E29?

 

[BarneyStinson]

This works if I am only choosing one value out of the month, but how would I use it if I wanted it to pick up the values in the range E2:E29?

Ok, I get what you want to do and why you thought of Arrays but I think a loop will work easier.

Good Luck:

Sub Multiple_Pivot_Fields()
Dim i As Variant

'Enables ability to select multiple items from list
With ActiveSheet.PivotTables("PivotTable1").PageFields("Transaction Date")
.EnableMultiplePageItems = True
End With

'Clears all filters
Sheets("Reference").PivotTables("PivotTable1").PivotFields("Transaction Date").ClearAllFilters
'unselects all "(blanks)" from list
Sheets("Reference").PivotTables("PivotTable1").PivotFields("Transaction Date").PivotItems("(blank)").Visible = False

'loops through defined data to unselect, to select this data, change "False" to "True"
For i = 2 To 29
Range("E" & i).Select
Sheets("Reference").PivotTables("PivotTable1").PivotFields("Transaction Date").PivotItems(Format(ActiveCell, "#")).Visible = False
Next
' Oh and I added the Format in the line above because my pivot had numbers as text, but if your pivot actually has numbers, then you shouldn't use format
End Sub

 

[MEasterbro]

Ok, I get what you want to do and why you thought of Arrays but I think a loop will work easier.

Good Luck:

Sub Multiple_Pivot_Fields()
Dim i As Variant

'Enables ability to select multiple items from list
With ActiveSheet.PivotTables("PivotTable1").PageFields("Transaction Date")
.EnableMultiplePageItems = True
End With

'Clears all filters
Sheets("Reference").PivotTables("PivotTable1").PivotFields("Transaction Date").ClearAllFilters
'unselects all "(blanks)" from list
Sheets("Reference").PivotTables("PivotTable1").PivotFields("Transaction Date").PivotItems("(blank)").Visible = False

'loops through defined data to unselect, to select this data, change "False" to "True"
For i = 2 To 29
Range("E" & i).Select
Sheets("Reference").PivotTables("PivotTable1").PivotFields("Transaction Date").PivotItems(Format(ActiveCell, "#")).Visible = False
Next
' Oh and I added the Format in the line above because my pivot had numbers as text, but if your pivot actually has numbers, then you shouldn't use format
End Sub

This works if you have 29 items in your pivot table, but what if you have 29,000?