Solved

Excel 2007 Pivot Table Filter Based On Dynamic Cell

Posted on 2011-03-07
2
919 Views
Last Modified: 2012-05-11
Hello Experts - Please see attached file.  I'd like to know if there is a way to based multiple pivot table filter selections on a specific cell.  Based on the attached sample file, if someone was to chose a different month in cell M2 on the first tab, all of the filters in the 3 pivot tables would change to that new month.

Thanks!!!
EE-Sample-File.xlsm
0
Comment
Question by:Escanaba
2 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 35057369
Right-click the worksheet tab, choose View Code then paste this in:
Private Sub Worksheet_Change(ByVal Target As Range)
   Dim pt As PivotTable
   Dim pf As PivotField
   On Error GoTo err_handle

   With Application
      .ScreenUpdating = False
      .EnableEvents = False
   End With
   If Not Intersect(Target, Me.Range("M2")) Is Nothing Then
      For Each pt In Me.PivotTables
         pt.ManualUpdate = True
         Set pf = pt.RowFields(1)
         pf.ClearAllFilters
         pf.PivotFilters.Add Type:=xlCaptionEquals, Value1:=Range("M2").Value
         pt.ManualUpdate = False
      Next pt
   End If
clean_up:
   With Application
      .EnableEvents = True
      .ScreenUpdating = True
   End With
   Exit Sub

err_handle:
   MsgBox Err.Description
   Resume clean_up
End Sub

Open in new window

0
 
LVL 1

Author Closing Comment

by:Escanaba
ID: 35057481
As always, thank you for your quick and accurate response.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question