We help IT Professionals succeed at work.

Excel VBA change pivot table filter using a named range's cell value (single and multi select)

761 Views
Last Modified: 2017-04-30
Dim DeptV As String
    DeptV = Range("PT_DeptV")   'this variable results in a text value  such as "08" or "33"
    Sheet2.PivotTables("PT_KL").PivotFields("Dpt").CurrentPage = DeptV    'this changes the 'Dpt' which is a filter on the PivotTable

I'm stuck on finding a solution on how the above process can work so if the range "PT_DeptV" equals "Roll" it would change the 'Dpt' filter in PivotTable "PT_KL" from a single value to multiple values (such as: "08", "33", ....) which will be located in a range named "Dpts".  The list of departments in the range "Dpts" goes down, not across.  If the range "PT_DeptV" does not equal "Roll" then it would change the 'Dpt' filter to the single value in range "PT_DeptV".
Comment
Watch Question

gowflowPartner
CERTIFIED EXPERT

Commented:
Can you post the file that have these settings/pivot ?
gowflow
CERTIFIED EXPERT
Top Expert 2010
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Here is a sample file.

Author

Commented:
Thanks Patrick Matthews, I tried your code but it failed on line 12 "Set pt = ThisWorkbook.Worksheets("Sheet2").PivotTables(1).  I tried changing that (1) to ("PT_KL") which is the name of the table but it still didn't work. then I changed it to this: Sheet2.PivotTables("PT_KL") and it runs but changes the Dpt filter to (All)
gowflowPartner
CERTIFIED EXPERT

Commented:
I see this has not been addressed for 2 days.
1 question in the file you posted there is NO Pivot table is this normal ? as all you talk about is a pivot ! this is why you get an error
Set pt = ThisWorkbook.Worksheets("Sheet2").PivotTables(1)

simply because there is no pivot !

Also I just checked your named ranges and lots of #REF this can be due to you having deleted sheets before posting this workbook ... so it is all messed up !!!! Open name manager in the file you posted here and see.
gowflow

Author

Commented:
I solved it.  I'll post a sample of the solved method in the near future.
gowflowPartner
CERTIFIED EXPERT

Commented:
Fine if you got your answer that's great.
gowflow
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
gowflowPartner
CERTIFIED EXPERT

Commented:
good u need anything else ?

Author

Commented:
Thanks gowflow. No I am all set and will now close the question.  I just re-read your earlier comment.  The original sample file "Dpt_Change_Sample" had the pivot table on the Data tab, sheet2.
gowflowPartner
CERTIFIED EXPERT

Commented:
Sorry open the file you posted and you will see there is no Pivot !
gowflow

Author

Commented:
Hmmm, I just opened it and could see it.  Try selecting Data!E1.  That is the pivot table's filter on the on the first file I posted.  The 'field list' is not showing by default.  I'm curious and would like to know if you still don't see.
gowflowPartner
CERTIFIED EXPERT

Commented:
I was talking about Dpt_Chg_sample.xlsm  when I made my comment.
the file you posted later is: SolutionSample.xlsm and it has the pivot.

gowflow

Author

Commented:
I see.  I checked the dpt_chg file when following up on your comment.  The solution file has four pivot tables, dpt_chg has just  the 1.

Author

Commented:
I figured out a solution on my own after it was not answered and I could not find a solution elsewhere.

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.