Excel VBA Macro from Pivot table

Question

I am trying to generate few reports from Excel Pivot Table where I have Pivot Table and trying to write a VBA code to develop Macro so that it automatically generate the report for 25 Branches where I need to send the report to. Possibly using Macro I can automate the email too.

Can any one help where to start from?

I got following code from Pivot table

Sub printit()
    Dim pt As PivotTable, pi As PivotItem, pf As PivotField
    Dim lLoop As Long

    Set pt = Sheet1.PivotTables(1)
    Set pf = pt.PageFields(1)

    For Each pi In pf.PivotItems
        Sheet1.PivotTables(1).PageFields(1).CurrentPage = pi.Value
        Sheet1.PrintOut
        lLoop = lLoop + 1
    Next pi
End Sub

Which I have changed to following according to my worksheet

Sub PrintAllPivotFilters()
    Dim pt As PivotTable, pi As PivotItem, pf As PivotField
    Dim lLoop As Long

    Set pt = Sheet3.Certifications
    Set pf = pt.Branch

    For Each pi In pf.PivotItems
        Sheet1.Certifications.Branch.CurrentPage = pi.Value
        Sheet1.PrintOut
        lLoop = lLoop + 1
    Next pi
End Sub

Answer 1

You can use showPages method of pivottable

Info: PivotTable.ShowPages Method

Creates a new PivotTable report for each item in the page field. Each new report is created on a new worksheet.

Syntax expression . ShowPages( PageField )

expression A variable that represents a PivotTable object.

[Optional parameter of pageField.]

---

Code:

ThisWorkbook.Worksheets("Sheet1").PivotTables(1).ShowPages "name of pagefield"

For a fuller example see here or here.

You can then use simple code to loop over these sheets and send each sheet as an e-mail attachment. See the following for getting you started with then e-mailing Mail from Excel with Outlook (Windows).

Remember you can easily reference each newly created sheet for export with your existing code

For Each pi In pf.PivotItems
    Worksheets(pi.Value) '<==pass this to sub that e-mails   
Next pi