Hi, I have a pivot table with 3 page fields, PageF1, PageF2 and PageF3 Each has a number of values including (All)
I want to go through every combination of the pagefields and if the pivot table has actual values in the data region to output to a pdf file with a filename format: Pagef1 - PageF2 - PageF3 .pdf The code goes through each page combination, but I cant exclude page fields with (all) i cant capture the names of the page fields of the combinations that have data in the data body range in a filename and use that filename to output the pivottable I am using the following code that i found at http://www.contextures.com/xlPivot09.html Your help is welcomed Sub PrintPivotPages() 'prints a copy of pivot table for 'each item in page field On Error Resume Next Dim pt As PivotTable Dim File As Variant 'somehow this would be used to define the filename need Dim pf As PivotField Dim pi As PivotItem Dim RngToSum As Range Set pt = ActiveSheet.PivotTables.Item(1) Set RngToSum = Nothing For Each pf In pt.PageFields For Each pi In pf.PivotItems pt.PivotFields(pf.Name).CurrentPage = pi.Name 'this is the data range of the pivottable Set RngToSum = pt.DataBodyRange If Application.WorksheetFunction.Sum(RngToSum) <> 0 Then ActiveSheet.PrintPreview 'use this for testing 'Specify acrobat file name and type 'This portion does not work, need help: Set File = pi.value.pdf 'ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= File, _ 'Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _ ':=False, OpenAfterPublish:=True End If Next Next pf End Sub -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,700 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
