Printing pivot table along with a copy and paste for each page fie

J

Jennifer

Well it is pivot tables for me tonight. Hope someone can help. I have:
Printing pivot table code for each page field but when the page field
changes i need to copy and paste (just values)the pivot table to another
worksheet and then print a chart that corresponds to that database. This code
is what i have but it just runs indefinately. Yuck!

Sub PrintPivotPages()
'prints a copy of pivot table for each item in page field
'assumes one page field exists
On Error Resume Next
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set pt = ActiveSheet.PivotTables.Item(1)
For Each pf In pt.PageFields
For Each pi In pf.PivotItems
pt.PivotFields(pf.Name).CurrentPage = pi.Name
ActiveSheet.PrintOut 'use this for printing
'ActiveSheet.PrintPreview 'use this for testing
With Worksheets("PivotReport")
.Range("PivotTableReport").Copy
End With
With Worksheets("PivotDatabase")
.Range("a1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End With
Next
Next pf
End Sub
 
D

Dave Peterson

Maybe removing the "on error resume next" line would make any error pop out.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top