Solved

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

Posted on 2010-09-10
5
1,404 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 20

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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 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

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now