Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VBA - Filter Pivot Based on Cell Reference - Check For No Values

Posted on 2010-09-10
5
Medium Priority
?
1,504 Views
Last Modified: 2012-05-10
I have the code below that auto filters a pivot table, "pvtTwo", based on a cell reference.  The cell references another pivot table, ("pvtOne").  So basically selecting a value to filter by in pivot table 1 auto filters for the same value in pivot table 2.
I'm noticing that when the value to filter by in pivot table 1 is not available in the data source of pivot table 2, it writes over the previously selected value in pivot table 2 that was showing.  Is there any way I can amend the code below to not re-label the value in Pivot 2?
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    On Error Resume Next
    Application.EnableEvents = False
    Me.PivotTables("pvtTwo").PivotFields ("State").CurrentPage = Range("AI7").Value
    Application.EnableEvents = True
End Sub

Open in new window

0
Comment
Question by:error_prone
  • 2
  • 2
5 Comments
 
LVL 21

Expert Comment

by:alainbryden
ID: 33649785
Perhaps you could upload a sample file that demonstrates your problem.
0
 

Author Comment

by:error_prone
ID: 33649864
Here's a sample.  Notice that when you select "Orange" from the pivot on the left, (One), then the other pivot correctly also filters to Orange, (with 120 units).  But if you select "Banana" from pivot One, it renames Orange in pivot Two to Banana, (because Banana does not exist in Two).  I want it to show an empty pivot if the value is not available, (or something like that).  But it should not interfere with the ability to filter in Pivot One.
TEST.xlsm
0
 
LVL 39

Accepted Solution

by:
nutsch earned 2000 total points
ID: 33650151
Try this update to your code, to validate existence before update.

Thomas

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    On Error Resume Next
    Application.EnableEvents = False
    Dim pt As PivotItem
    For Each pt In Me.PivotTables("pvtTwo").PivotFields("Fruit").PivotItems
        If pt.Name = Range("B8").Value Then
            Me.PivotTables("pvtTwo").PivotFields("Fruit").CurrentPage = Range("B8").Value
            GoTo endline:
        End If
    Next pt
    MsgBox "No matching data"
endline:
    Application.EnableEvents = True
End Sub

Open in new window

0
 

Author Closing Comment

by:error_prone
ID: 33650176
Perfect, thank you!!
0
 
LVL 39

Expert Comment

by:nutsch
ID: 33650251
Glad to help, thanks for the grade.

Thomas
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

783 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