Hello
I am trying to print out individual reports from a pivot table where
the changing value will be in the column field (the field that can be
found at the top of the pivot table. The info for the table comes from
our accounting department and can be automatically updated, but to
simplify the printing I would like that to be semi automatic, without
going through each name. Any ideas ?
Thanks
Stan

That's called a page field.

See Debra Dalgleish for some sample code:
http://contextures.com/xlPivot09.html#Page


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

wrote:

Hello
I am trying to print out individual reports from a pivot table where
the changing value will be in the column field (the field that can be
found at the top of the pivot table. The info for the table comes from
our accounting department and can be automatically updated, but to
simplify the printing I would like that to be semi automatic, without
going through each name. Any ideas ?
Thanks
Stan

--

Dave Peterson

Dave Peterson wrote:
That's called a page field.

See Debra Dalgleish for some sample code:
http://contextures.com/xlPivot09.html#Page


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Thanks Dave:
I found her web site and thought I found a macro that would help, it
went through the names on the page field. That worked great but at the
end of the list it kept showing the last account without stopping.
The macro is short so I will reprint it here and obviously give credit
to Debra Dalgeish.

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
Next
Next pf ' does not stop automatically at the end
of the macro.
End Sub

what needs to be added to finish the macro and return to Excel?

Thanks
Stan

Debra uses printpreview in her code to save paper while testing.

If you comment out the .printpreview and uncomment the line above, you'll have
printed reports for all the page items.

To comment a line, just put an apostrophe in front of it. To uncomment a line,
remove that first apostrophe.

(If I understood what you meant, that is???)

Stan wrote:

Dave Peterson wrote:
That's called a page field.

See Debra Dalgleish for some sample code:
http://contextures.com/xlPivot09.html#Page


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Thanks Dave:
I found her web site and thought I found a macro that would help, it
went through the names on the page field. That worked great but at the
end of the list it kept showing the last account without stopping.
The macro is short so I will reprint it here and obviously give credit
to Debra Dalgeish.

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
Next
Next pf ' does not stop automatically at the end
of the macro.
End Sub

what needs to be added to finish the macro and return to Excel?

Thanks
Stan

--

Dave Peterson

Thanks Dave:
I did look at it through print preview and it continued to run... If
you say that during the actual printing it will stop at the end then
that is what I want. I was just scared of wasting reams of paper.
Thanks again for your quick reply.
Stan

How about testing it with a small amount of data--no need to kill too many trees
<vbg.

Stan wrote:

Thanks Dave:
I did look at it through print preview and it continued to run... If
you say that during the actual printing it will stop at the end then
that is what I want. I was just scared of wasting reams of paper.
Thanks again for your quick reply.
Stan

--

Dave Peterson