Print Pivot Table for Each Report Filter

[jamesexcel1970]

Hello

Iam looking for Printing pivot table for each report filter working with a workbook, So this is manuel job for me at work.Any clue.

 

[Monty]

Hey James..

Not sure this helps, Done this at my work place.

Please let us no any challenges..Happy to help U.

Sub Print_pivot()

'Step 1: Declare your Variables
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem


'Step 2: Point to the pivot table in the activecell
    On Error Resume Next
    Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)


'Step 3:  Exit if active cell is not in a pivot table
    If pt Is Nothing Then
    MsgBox "You must place your cursor inside of a pivot table."
    Exit Sub
    End If


'Step 4:  Exit if more than one page field
    If pt.PageFields.Count > 1 Then
    MsgBox "Too many Report Filter Fields. Limit 1."
    Exit Sub
    End If


'Step 5:  Start looping through the page field and its pivot items
    For Each pf In pt.PageFields
        For Each pi In pf.PivotItems
      
'Step 6:  Change the selection in the report filter
        pt.PivotFields(pf.Name).CurrentPage = pi.Name
      
      
'Step 7: Set Print Area and print
        ActiveSheet.PrintOut Copies:=1
        Next pi
    Next pf

End Sub

 

[jamesexcel1970]

Hey little champ.

That was so quick
This is the best of Chandoo.
i will take a while to test this am not quick as you.
Any challenges i will definitely let you know.

 

[Monty]

My Pleasures James.

 

[jamesexcel1970]

Little Champ.

While i check, i have another quick question to create each pivot filter as a workbook.

Iam trying to do on my own, but still to have proper coding, for me only macro recording.

Cheers!

 

[Monty]

Hello

I Suggest to upload a sample file always to get proper solution...In case if u r beginner will be difficult to modify as per your requirements...Just Think.

 

[jamesexcel1970]

Champ.

Agree but helps us to learn vba this way, i can understand that difficulty for you guys to provide solutions without any workbook.

Hope you can understand where am coming from, for beginner perspective.

 

[Monty]

James, Here we go.

Pls change path.

Sub Create_Workbook_Pivot()

'Step 1: Declare your Variables
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem


'Step 2: Point to the pivot table in the activecell
    On Error Resume Next
    Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)


'Step 3:  Exit if active cell is not in a pivot table
    If pt Is Nothing Then
    MsgBox "You must place your cursor inside of a pivot table."
    Exit Sub
    End If
   
   
'Step 4:  Exit if more than one page field
    If pt.PageFields.Count > 1 Then
    MsgBox "Too many Report Filter Fields. Limit 1."
    Exit Sub
    End If


'Step 5:  Start looping through the page field and its pivot items
    For Each pf In pt.PageFields
        For Each pi In pf.PivotItems
               

'Step 6:  Change the selection in the report filter
        pt.PivotFields(pf.Name).CurrentPage = pi.Name
       
       
'Step 7: Copy the data area to a new workbook
        pt.TableRange1.Copy _
        Workbooks.Add.Worksheets(1).Paste
       
        Application.DisplayAlerts = False
          ActiveWorkbook.SaveAs _
          Filename:="C:\Monty\New\" & pi.Name & ".xlsx" 'Change path here
          ActiveWorkbook.Close
        Application.DisplayAlerts = True
       
       
'Step 8: Get the next page field item
        Next pi
    Next pf

End Sub

 

[jamesexcel1970]

Wow i can easily go with this and wanted to put little efforts to modify as per business.

Champ, if i send you workbook you will complete my job but i will never learn, don't take me wrong.

Will confirm back to shoot you.

Can you please take a look at this as you don't sleep and other's.

http://chandoo.org/forum/threads/adding-zero-as-prefix-cells-with-zeros.33245/

Cheers!

 

[Monty]

Hmmmm... Good night feeling sleepy...Worried about Monday morning.
But I will answer before I sleep tonight.

 

[jamesexcel1970]

Thanks a ton.............................God bless you.

 

[jamesexcel1970]

Below code works faster as expected.
Need a change wanted this to work on its own, i meen.. i do not want to change the filter and click button. have a best sleep.

Am not in hurry! Cheers.

James, Here we go.

Pls change path.

Sub Create_Workbook_Pivot()

'Step 1: Declare your Variables
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem


'Step 2: Point to the pivot table in the activecell
    On Error Resume Next
    Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)


'Step 3:  Exit if active cell is not in a pivot table
    If pt Is Nothing Then
    MsgBox "You must place your cursor inside of a pivot table."
    Exit Sub
    End If
  
  
'Step 4:  Exit if more than one page field
    If pt.PageFields.Count > 1 Then
    MsgBox "Too many Report Filter Fields. Limit 1."
    Exit Sub
    End If


'Step 5:  Start looping through the page field and its pivot items
    For Each pf In pt.PageFields
        For Each pi In pf.PivotItems
              

'Step 6:  Change the selection in the report filter
        pt.PivotFields(pf.Name).CurrentPage = pi.Name
      
      
'Step 7: Copy the data area to a new workbook
        pt.TableRange1.Copy _
        Workbooks.Add.Worksheets(1).Paste
      
        Application.DisplayAlerts = False
          ActiveWorkbook.SaveAs _
          Filename:="C:\Monty\New\" & pi.Name & ".xlsx" 'Change path here
          ActiveWorkbook.Close
        Application.DisplayAlerts = True
      
      
'Step 8: Get the next page field item
        Next pi
    Next pf

End Sub