Dynamically changing pivot table print area

[Twee]

I have a pivot table that expands and contracts with data. I am

constantly changing print areas to accomodate all the data and to hide the report filters so it looks more like an actual report.


Does anyone know how a macro that I could use that could help me:


1. Print the pivot table without the report filter

2. Accomodate constantly changing print area depending on item selection

2. Those changes would translate even when I use my show report filter pages


I'm an intermediate excel user and can record macros so I'm kind of ill equipped to solve this issue. Any help would be so appreciated!


Twee

 

[Hui]

You Can change the Print_Area named formula to a formula like

=OFFSET(Sheet1!$D$3,,,COUNTA(Sheet1!$D$3:$D$10000),COUNTA(Sheet1!$D$3:$ZZ$3))

Where D3 is the Top Left Corner of the Pivot Table area

Adjust the sheet name and Rows/Columns to suit

 

[Twee]

I'm glad to see a solution that doesn't require macro, but do i use this named range for every page/worksheet produced via the Show Report Filter pages? Sorry, i just wanted to clarify. Thanks, Hui, for the help.

 

[Hui]

Twee

On sheet 2 you will change it to be

=OFFSET(Sheet2!$D$3,,,COUNTA(Sheet2!$D$3:$D$10000),COUNTA(Sheet2!$D$3:$ZZ$3))


Noting that each sheet has a named Formula called Print_Area

which has a Scope of that sheet only

 

[Twee]

Hi Hui,

Thanks for the clarification. For some reason, I couldn't get the printing correct.


I named the Print_Area: =OFFSET(WorkingPivot!$A$5,,,COUNTA(WorkingPivot!$A$5:$A$1000),COUNTA(WorkingPivot!$A$5:$Z$5))


A5 is the left most corner of my pivot table. I am not sure what I'm doing incorrectly. Thanks for any help you can offer.


Thuy

 

[Hui]

You may need to Change

the Row: COUNTA(WorkingPivot!$A$5:$Z$5) to the row with the titles

eg: COUNTA(WorkingPivot!$A$6:$Z$6)

and the column

the Row: COUNTA(WorkingPivot!$A$5:$A$1000) to the Column with the titles

eg: COUNTA(WorkingPivot!$B$5:$B$1000)


if you have multiple levels on either axis of the pivot table

 

[Twee]

Thanks, Hui. I am pretty sure I made the right change but glad to e-mail it to you. Thank you so much!!


Just sent it to you.

 

[Hui]

Because WorkingPivot!A5 is blank you need to allow for that

so use:

=OFFSET(WorkingPivot!$A$5,,,COUNTA(WorkingPivot!$A$5:$A$1000)+1,COUNTA(WorkingPivot!$A$5:$Z$5)+1)

 

[Twee]

Thanks, Hui!! I still get the report filter on top in my printout but I'll figure out something. Your help really makes me think that I need to practice the offsset function more. Thanks!


Twee

 

[prasadmadkaikar]

I want to create individual worksheets from a pivot table but the Show Report Filter Pages option is greyed out. Looking forward to your help/advice.


-Prasad