Emailing Pivot tables (filtered for different recipients)

Texas Longhorn

Active Member
Joined
Sep 30, 2003
Messages
493
I have a pivot table, for example:
Email Macros.xls
ABCD
3SumofProd/Serv
4ManagerTotal
5bettyw187,192
6frankg489,572
7markb233,589
8GrandTotal910,353
Pivot


I also have the following code:

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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Texas Longhorn

Active Member
Joined
Sep 30, 2003
Messages
493
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:

Code:
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:

Code:
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
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Re: Emailing Pivot tables (filtered for different recipients

Does this help?

Code:
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,102
Messages
5,768,104
Members
425,454
Latest member
khoro

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top