Pivot Table: Complex printing issue????

[Guest]

Hello Everyone

I have a pivot table that uses two page fields. I have written the code to automatically cycle through the page field items and print each on separate pages(code below, AND THANKS to several of you who helped me!). I have to update the data and print this weekly which consumes a lot of paper which is mostly wasted. I would like to have two additional options

1) I would like to send each output to a new worksheet in a new workbook which I can then distribute instead of wasting paper

2) Would it be possible to send each to the same worksheet without overwriting each other? Note that each output is not of the same number of rows

Thanks for your help. My current code is below: (Note: I know my code is probably inefficent, but it works. If you have comments on making this code more efficent please email me at (e-mail address removed) so this post can on track

On Error Resume Nex
Dim pt As PivotTabl
Dim pf As PivotFiel
Dim pi As PivotIte
Dim pfarray(25
Dim piarray(25, 50

Set pt = ActiveSheet.PivotTables("LT_Two"

i =

For Each pf In pt.PageField
pfarray(i) = pf.Nam
j =
For Each pi In pf.PivotItem
piarray(i, j) = pi.Nam
y =
j = j +
Nex
x =
i = i +
Nex

j =

For p = 1 To
i =
j = j +
pt.PivotFields(pfarray(i)).CurrentPage = piarray(i, j
i = i +
For j = 1 To
pt.PivotFields(pfarray(i)).CurrentPage = piarray(i, j

Set TableRange = Range("AP6", Range("AP" & Rows.Count).End(xlUp).Offset(, 14)
TableRange.PrintOut 'use this for printin
' TableRange.PrintPreview 'use this for testin
Nex
i =
j =
Next

 

[Tom Ogilvy]

On thing you need to do is fix you macro so it actually works correctly.
Unless your have the same number of pivot items in each pagefield, your code
either sets the first pivot field to blank values or misses some of the
values since Y will have the value of the number of pivot items in the
second pagefield. Y should be an array the same size as pfarray

Dim y(25)


y(i) = j



Then later

For j = 1 To y(i)


After that, you can

Set TableRange = Range("AP6", Range("AP" & Rows.Count).End(xlUp).Offset(,
14))
tablerange.copy
set sh = Workbooks("Book2.xls").Worksheets(1)
set rng = sh.cells(rows.count,1).End(xlup)(2)
rng.pasteSpecial xlValues
rng.pasteSpecial xlformats

--
Regards,
Tom Ogilvy

Hello Everyone,

I have a pivot table that uses two page fields. I have written the code to

automatically cycle through the page field items and print each on separate
pages(code below, AND THANKS to several of you who helped me!). I have to
update the data and print this weekly which consumes a lot of paper which is
mostly wasted. I would like to have two additional options.

1) I would like to send each output to a new worksheet in a new workbook

which I can then distribute instead of wasting paper.

2) Would it be possible to send each to the same worksheet without

overwriting each other? Note that each output is not of the same number of
rows.

Thanks for your help. My current code is below: (Note: I know my code is

probably inefficent, but it works. If you have comments on making this code
more efficent please email me at (e-mail address removed) so this post can
on track)

On Error Resume Next
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim pfarray(25)
Dim piarray(25, 50)

Set pt = ActiveSheet.PivotTables("LT_Two")

i = 1

For Each pf In pt.PageFields
pfarray(i) = pf.Name
j = 1
For Each pi In pf.PivotItems
piarray(i, j) = pi.Name
y = j
j = j + 1
Next
x = i
i = i + 1
Next

j = 0

For p = 1 To x
i = 1
j = j + 1
pt.PivotFields(pfarray(i)).CurrentPage = piarray(i, j)
i = i + 1
For j = 1 To y
pt.PivotFields(pfarray(i)).CurrentPage = piarray(i, j)

Set TableRange = Range("AP6", Range("AP" &

Rows.Count).End(xlUp).Offset(, 14))