Macro to filter Pivot Table by multiple cells in another tab?

[NeesiePie]

Please be kind, as I am new here and new to VBA in general. I have an Excel 2007 problem I’m trying to solve with macros and I’m hoping someone can help me with the code. Our company is structured as follows (except on a much larger scale, obviously):</SPAN>

[TABLE="width: 300"]
<TBODY>[TR]
[TD]Mgr A
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Supv A
[/TD]
[TD]Supv B
[/TD]
[/TR]
[TR]
[TD]Emp 1
[/TD]
[TD]Emp 1
[/TD]
[/TR]
[TR]
[TD]Emp 2
[/TD]
[TD]Emp 2
[/TD]
[/TR]
[TR]
[TD]Emp 3
[/TD]
[TD]Emp 3
[/TD]
[/TR]
[TR]
[TD]Emp 4
[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

[TABLE="width: 300"]
<TBODY>[TR]
[TD]Mgr B
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Supv C
[/TD]
[TD]Supv D
[/TD]
[/TR]
[TR]
[TD]Emp 1
[/TD]
[TD]Emp 1
[/TD]
[/TR]
[TR]
[TD]Emp 2
[/TD]
[TD]Emp 2
[/TD]
[/TR]
[TR]
[TD]Emp 3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

I need to create a separate pivot table (in a separate tab) for each person. I have a tab with names and titles that I can use as a reference, but I don’t know how to write the code. Currently I’m manually creating a pivot for Supv A, going to the Names tab and filtering to get his employees, going back to the pivot and filtering by checking off the names, and then creating pivots for all checked. There has to be an easier way. I’d like be able to do it all with macros because of the number of people involved. I apologize for my convoluted explanation, but if anyone can help with code I’d be so grateful. </SPAN>

 

[Ferdi24]

I don't really understand what it is I am looking at here or what you are trying to achieve.
That could be reason for 0 replies so far :-\

I need to create a separate pivot table (in a separate tab) for each person.

Which information should it have, etc?

 

[NeesiePie]

Sorry - I thought I'd explained it well but apparently not. I have the following code:

ActiveSheet.PivotTables("PivotTable23").PivotFields("Name").CurrentPage = _
"(All)"
With ActiveSheet.PivotTables("PivotTable23").PivotFields("Name")
.PivotItems("AARON, BRUCE").Visible = False
.PivotItems("BALDWIN, JOSHUA").Visible = False
.PivotItems("BOLICK, JACK").Visible = False
.PivotItems("BROYLES, GREGORY").Visible = False
.PivotItems("BRYANT, MARCUS").Visible = False
.PivotItems("CANIPE, ROBERT").Visible = False
.PivotItems("DAVIS, CHARLES").Visible = False
.PivotItems("DAWSON, EVERETT").Visible = False
.PivotItems("DEMARTINO, DEAN").Visible = False
End With
ActiveSheet.PivotTables("PivotTable23").PivotFields("Name"). _
EnableMultiplePageItems = True
End Sub


Essentially I have to filter an existing pivot table (for Supv A) per names (Emps) on another worksheet, then create a new pivot table for each name. Right now I'm manually clicking the names to filter...I'm wondering if there's some way to tell Excel to refer to this list of names without me having to click each one... something like "IF SupvA = Johnson, create pivot table", except in VBA.

Thanks so much for your help and patience with my poor explanations.

 

[Ferdi24]

Hi NeesiePie,

this code will filter the Pivot Table pt with all names in ListOfNames (you can define these at the beginning).
You should be able to build the rest of your code based on this.

Option Explicit


Sub PivotFilter()
Dim cName As Range
Dim ListOfNames As Range
Dim pt As PivotTable
Dim pi As PivotItem


Set ListOfNames = Range("B3:B9") 'Put the List of Names in here
Set pt = ActiveSheet.PivotTables("PivotTable1") 'This is the pivot table


pt.ManualUpdate = True 'Speeds it up a little
On Error Resume Next 'Just in case the item you want to remove is the last one. A pivot must have at least one item.
For Each pi In pt.PivotFields("Name").PivotItems
    If IsError(Application.WorksheetFunction.Match(pi.Name, ListOfNames, 0)) Then
        pi.Visible = False
   'Optional, will slow it down a bit:
   ' Else
   '     pi.Visible = True
    End If
Next pi
pt.ManualUpdate = False
End Sub