Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table: Complex printing issue????
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 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table: Complex printing issue????
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 "Joe Mathis" wrote in message ... 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 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)) TableRange.PrintOut 'use this for printing ' TableRange.PrintPreview 'use this for testing Next i = 0 j = 1 Next |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
One pivot table cannot overlap another pivot issue. | Excel Discussion (Misc queries) | |||
Pivot Table Issue | Excel Worksheet Functions | |||
Complex Pivot Table Function | Excel Discussion (Misc queries) | |||
Pivot table sum issue | Excel Discussion (Misc queries) | |||
Complex Pivot Table | Excel Discussion (Misc queries) |