We help IT Professionals succeed at work.

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

error_prone
error_prone asked
on
1,575 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

Comment
Watch Question

Perhaps you could upload a sample file that demonstrates your problem.

Author

Commented:
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
Top Expert 2008
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Perfect, thank you!!
Top Expert 2008

Commented:
Glad to help, thanks for the grade.

Thomas

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.