Excel VBA Macro from Pivot table

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

1 answer

  • answered 2018-07-12 07:29 QHarr

    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