Change a Pivot Table Report Filter Using a Data Validation List

louisgeorgiou

New Member
Joined
Apr 13, 2010
Messages
3
I have a spreadsheet that has multiple work sheets which each have a pivot table with Year and Month Report Filters.
I'd like to be able to select the Year and Month via Data Validation Lists on the front sheet (my dashboard) and automatically have the Pivot Tables update in the other work sheets.
How would this be possible? Via VBA only?
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,191
Hi, Louis

You could use VBA. Please refer http://www.contextures.com/excelfiles.html#Pivot

Fully automatic without VBA will be limiting, such as set up the pivot table to query external data and refresh every n minutes.

Or, you could have minimal VBA (just one line?) to refresh of pivot tables on change to either of the data validation cells. For this, set up the pivot tables by joining the current source data with the data validation cells. SQL like below. VBA would be something like this pseudo code

sub worksheet_change(target as range)
if to identify target is year or month data validated cell then wbk.refreshall
end sub

HTH, Fazza

Code:
SELECT s.fields
FROM source s, validation_cells v
WHERE s.year = v.year AND s.month = v.month
 

LONeill13

Board Regular
Joined
Feb 12, 2013
Messages
132
I am trying to run this code, but it's not working....even on the sample excel file that is provided in post# 3. Any idea why the code is not running? Thanks
 

thomas93

New Member
Joined
Apr 7, 2017
Messages
16
Hi,

Look like the kind of codes I am looking for but sadly when I open the excel file from which the link is in this discussion, it doesn't work. I tried to implement it on my huge file and it doesn't either.. any idea why??
 

Forum statistics

Threads
1,081,851
Messages
5,361,688
Members
400,648
Latest member
mamamia93

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top