VBA Code to Update Pivot Table Filters

torijm

New Member
Joined
Jun 20, 2012
Messages
4
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.
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Etv5002

New Member
Joined
Sep 15, 2011
Messages
30
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

New Member
Joined
Jun 20, 2012
Messages
4
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.
 

BarneyStinson

New Member
Joined
Jun 27, 2012
Messages
2

ADVERTISEMENT

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

New Member
Joined
Jun 20, 2012
Messages
4
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

New Member
Joined
Jun 27, 2012
Messages
2
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

New Member
Joined
Nov 23, 2016
Messages
2
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,139
Messages
5,599,958
Members
414,352
Latest member
macquarie_jchan58

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top