I have created a PowerPivot Table in Excel 2013 which extracts data every 24 hrs from a company database. Unfortunately, the data I extract contains information for the entire company and I only need information for about 90 employees.
I have created a PivotTable named "User_Report" on a worksheet called "User_Chart". I want to filter out employee names in the Pivot Table which are located in an Active Field named "User_Name". On a separate spreadsheet, I have a list of employees located in a Table called "User_Table" which is on a worksheet called "Sales_Roster_Filter".
I have created a Command Button with a VBA to filter the Pivot Table with the list of employees I need with the following code:
Sub Test()
Dim PI As PivotItem
With Worksheets("User_Chart").PivotTables("User_Report").PivotFields("User_Name")
.ClearAllFilters
For Each PI In .PivotItems
PI.Visible = WorksheetFunction.CountIf(Range("User_Table"), PI.Name) > 0
Next PI
End With
End Sub
Unfortunately, this code does not appear to do anything. If anyone has any suggestions, it would be much appreciated, I would prefer not having to manually select the names I need from a list of thousands. If I can clarify my issue further, please let me know. Thanks.
*****Please Note: The spelling of the employee names in the PivotTable (the names pulled from the database) is not an exact match with the spelling of the names in the table I created, but is accurate enough that an approximate match should work*****
I have created a PivotTable named "User_Report" on a worksheet called "User_Chart". I want to filter out employee names in the Pivot Table which are located in an Active Field named "User_Name". On a separate spreadsheet, I have a list of employees located in a Table called "User_Table" which is on a worksheet called "Sales_Roster_Filter".
I have created a Command Button with a VBA to filter the Pivot Table with the list of employees I need with the following code:
Sub Test()
Dim PI As PivotItem
With Worksheets("User_Chart").PivotTables("User_Report").PivotFields("User_Name")
.ClearAllFilters
For Each PI In .PivotItems
PI.Visible = WorksheetFunction.CountIf(Range("User_Table"), PI.Name) > 0
Next PI
End With
End Sub
Unfortunately, this code does not appear to do anything. If anyone has any suggestions, it would be much appreciated, I would prefer not having to manually select the names I need from a list of thousands. If I can clarify my issue further, please let me know. Thanks.
*****Please Note: The spelling of the employee names in the PivotTable (the names pulled from the database) is not an exact match with the spelling of the names in the table I created, but is accurate enough that an approximate match should work*****
Last edited:
