HellsBells
New Member
- Joined
- Sep 21, 2006
- Messages
- 1
Hi
I found this tip for a print macro to print the pages of a pivot table on this site in the archives. The problem is that I get a run time error before next i
I can get over this by ignoring the error but this is not really a good policy to employ. Does anyone have any ideas
Many Thanks
HellsBells
Sub PrintAll()
' Find name of page field
PageField1 = ActiveSheet.PivotTables("PivotTable1").PageFields(1)
' Save the name of the page field displayed when macro begins
OrigPage = ActiveSheet.PivotTables("PivotTable1").PivotFields(PageField1).CurrentPage
' Determine how many pages there are in page field
NumPages = ActiveSheet.PivotTables("PivotTable1").PivotFields(PageField1).PivotItems.Count
For i = 1 To NumPages
ThisPage = ActiveSheet.PivotTables("PivotTable1").PivotFields(PageField1).PivotItems(i)
ActiveSheet.PivotTables("PivotTable1").PivotFields(PageField1).CurrentPage = ThisPage
ActiveWindow.SelectedSheets.PrintOut
Next i
' Print the (all) page
ActiveSheet.PivotTables("PivotTable1").PivotFields(PageField1).CurrentPage = "(all)"
ActiveWindow.SelectedSheets.PrintOut
' Restore view back to original page
ActiveSheet.PivotTables("PivotTable1").PivotFields(PageField1).CurrentPage = OrigPage
End Sub
I found this tip for a print macro to print the pages of a pivot table on this site in the archives. The problem is that I get a run time error before next i
I can get over this by ignoring the error but this is not really a good policy to employ. Does anyone have any ideas
Many Thanks
HellsBells
Sub PrintAll()
' Find name of page field
PageField1 = ActiveSheet.PivotTables("PivotTable1").PageFields(1)
' Save the name of the page field displayed when macro begins
OrigPage = ActiveSheet.PivotTables("PivotTable1").PivotFields(PageField1).CurrentPage
' Determine how many pages there are in page field
NumPages = ActiveSheet.PivotTables("PivotTable1").PivotFields(PageField1).PivotItems.Count
For i = 1 To NumPages
ThisPage = ActiveSheet.PivotTables("PivotTable1").PivotFields(PageField1).PivotItems(i)
ActiveSheet.PivotTables("PivotTable1").PivotFields(PageField1).CurrentPage = ThisPage
ActiveWindow.SelectedSheets.PrintOut
Next i
' Print the (all) page
ActiveSheet.PivotTables("PivotTable1").PivotFields(PageField1).CurrentPage = "(all)"
ActiveWindow.SelectedSheets.PrintOut
' Restore view back to original page
ActiveSheet.PivotTables("PivotTable1").PivotFields(PageField1).CurrentPage = OrigPage
End Sub