Solved

Excel 2007 Pivot Table Filter Based On Dynamic Cell

Posted on 2011-03-07
2
920 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

732 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