• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

VBA Pivot Table Filter|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / VBA Pivot Table Filter|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosVBA Pivot Table Filter
sp_PrintTopic sp_TopicIcon
VBA Pivot Table Filter
Avatar
Kyle Jacobs

New Member
Members
Level 0
Forum Posts: 1
Member Since:
March 13, 2018
sp_UserOfflineSmall Offline
1
March 20, 2018 - 1:19 am
sp_Permalink sp_Print sp_EditHistory

I am trying to create a dashboard where the option the user picks in a combo box is filtered in all the pivot tables on another sheet.  The code seems to be working fine except when the filtered named doesn't exist in a specific pivot table.  What is happening is its forcing the Filter to be what the selection is whether it exists or not and then uses that data in the pivot table that was there from the previous filter as the data for the new non-existent filter.  When I realized this after going 1 by 1 through each selection, I tried to manually recalculate the pivot table and it wouldn't refresh as 0.  Then I went into the data table added that selection with 0s in the data fields but it still pulls the old filtered data almost like its force saved in the background. 

Any one have any idea on how to fix this? The code I have used is below. I should preface this with the fact that I am no coder at all; when ever I need code I look through forums and try to modify code I find until I get it to work through trial and error.

Private Sub ComboBox1_Change()

Dim sheet As Worksheet
Dim pt As PivotTable
Dim ptField As PivotField

Set sheet = ThisWorkbook.Worksheets("PivotTables")

For Each pt In sheet.PivotTables

Set ptField = Nothing

On Error Resume Next

Set ptField = pt.PivotFields("Provider Name")

ptField.CurrentPage = Me.ComboBox1.Value

Next pt

End Sub

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
2
March 20, 2018 - 10:20 am
sp_Permalink sp_Print

Hi Kyle

Welcome to the forum.

I would suggest you use a slicer instead of a combo box when dealing with Pivot Tables. You don't need codes at all and it is easy to implement.

There are many articles here at MOTH on this subject. Here is one of them https://www.myonlinetraininghu.....licer-that

Sunny

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
3
March 20, 2018 - 2:15 pm
sp_Permalink sp_Print sp_EditHistory

If you need codes, then maybe you can give this a try :

Private Sub ComboBox1_Change()
Dim ws As Worksheet
Dim wb As Workbook
Dim pt As PivotTable
Dim pf As PivotField
Set wb = ActiveWorkbook

For Each ws In wb.Worksheets
For Each pt In ws.PivotTables
Set pf = Nothing
On Error Resume Next
Set pf = pt.PivotFields("PROVIDER NAME")
'Clear Out Any Previous Filtering
pf.ClearAllFilters
'Set the filter
pf.CurrentPage = ComboBox1.Value
Next pt
Next ws
End Sub

Please note that the filter is set to ALL if the selected name is not found in the PT.

Good luck

Sunny

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: dan conner, Christoffer Sandberg
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 204
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Brett Dryland
Saeed Aldousari
Bhuwan Devkota
Kathryn Patton
Maria Conatser
Jefferson Granemann
Glen Coulthard
Nikki Fox
Rachele Dickie
Raj Mattoo
Forum Stats:
Groups: 3
Forums: 24
Topics: 6222
Posts: 27291

 

Member Stats:
Guest Posters: 49
Members: 31912
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.