Emailing Pivot tables (filtered for different recipients)

[Texas Longhorn]

I have a pivot table, for example:

Email Macros.xls
	A	B	C	D
3	SumofProd/Serv
4	Manager	Total
5	bettyw	187,192
6	frankg	489,572
7	markb	233,589
8	GrandTotal	910,353
Pivot

I also have the following code:

Sub EmailPivot()
Sheets("Pivot").Copy
ActiveWorkbook.SendMail Recipients:="billis@texas.com", Subject:="Testing"
ActiveWorkbook.Close False
End Sub

What I would like to do, is add to this code such that I can sort the pivot table for bettyw, and send just this data to bettyw@texas.com. Then I want to sort the pivot table on frankg, and send his data to frankg@texas.com. Etc...

Any thoughts are appreciated.

Thanks,

Bill

 

[Texas Longhorn]

Re: Emailing Pivot tables (filtered for different recipients

Ok, I've got everything working now, but I'm hoping there is a better way to work this. Here is my code for manipulating the pivot table:

Sheets("Weekly Pipeline Changes Q1").Select
    With ActiveSheet.PivotTables("WeeklyChange").PivotFields("Manager")
        .PivotItems("agomes").Visible = False
        .PivotItems("apexmngr").Visible = False
        .PivotItems("bstephenso").Visible = False
        .PivotItems("kcoccio").Visible = False
        .PivotItems("mmoseley").Visible = False
        .PivotItems("mweaver").Visible = False
        .PivotItems("rolamngr").Visible = False
        .PivotItems("sguidry").Visible = False
        .PivotItems("tmisch").Visible = False
        .PivotItems("#N/A").Visible = False
        .PivotItems("(blank)").Visible = False
        .PivotItems("rwhite").Visible = False
        .PivotItems("tmisch_c").Visible = False
    End With

And then the code to put it back how it was:

With ActiveSheet.PivotTables("WeeklyChange").PivotFields("Manager")
        .PivotItems("agomes").Visible = True
        .PivotItems("apexmngr").Visible = True
        .PivotItems("bstephenso").Visible = True
        .PivotItems("kcoccio").Visible = True
        .PivotItems("mmoseley").Visible = True
        .PivotItems("mweaver").Visible = True
        .PivotItems("rolamngr").Visible = True
        .PivotItems("sguidry").Visible = True
        .PivotItems("tmisch").Visible = True
        .PivotItems("#N/A").Visible = True
        .PivotItems("(blank)").Visible = True
        .PivotItems("rwhite").Visible = True
        .PivotItems("tmisch_c").Visible = True
    End With

There are 14 managers in my "Manager" pivot field (the code above sorted the pivot table on "bschaefe" only; the second bit of code put the pivot table back to where it shows all 14.

The reason I'm hoping for better code is that all of these individual listings will become a pain as managers are hired/separated.

Is there a way to adjust this code so the first bit says something to the effect of "show only "bschaefe", and then the second batch says "show all"?

Many thanks,

Bill

 

[Texas Longhorn]

Anyone on the afternoon crew have any thoughts?

 

[DonkeyOte]

Re: Emailing Pivot tables (filtered for different recipients

Does this help?

Sub a()

Dim PT As PivotTable
Dim PF As PivotField
Dim PI As PivotItem

Set PT = ActiveSheet.PivotTables(1)

For Each PI In PT.PivotFields(1).PivotItems
    On Error GoTo Problemo:
    If PI.Value <> "a" Then 'set "a" as a variable - ie name in the field
        PI.Visible = False
    End If
Next

For Each PI In PT.PivotFields(1).PivotItems
    PI.Visible = True
Next

Exit Sub

Problemo:
MsgBox Err.Description
Resume Next

End Sub

You'd need to change the PT reference etc and the PF index to match your criteria but it should point you in the right direction I hope.

 

[Texas Longhorn]

lasw10,

Thanks for your help -- it is extremely appreciated.

Thanks,

bill