Dear TheoMa, one suggestion here:- Install Adobe Acrobat professional and then you can convert the each page output as PDF in a single click.
Let me know if other information is required on this line. Best Regards, -- DILIP KUMAR PANDEY MBA-HR,B Com(Hons.),BCA Mobile: +91 9810929744 dilipan...@gmail.com dilipan...@yahoo.com New Delhi - 62, India On 1/30/10, TheoMa <theodore_douca...@hotmail.com> wrote: > > 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 > -- ---------------------------------------------------------------------------------- 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