Dynamic Printing of Pivot Table

03856me

Active Member
Joined
Apr 4, 2008
Messages
277
I am sorry if this is already addressed elsewhere - I have searched and read hundreds of posts and yet to find what I need. This seems so simple yet.....

I have code written to sync the page fields on many worksheets based on the main pivot table selection. I would like to group and print all the pivot tables once a different page field is selected, no problem, except I need code to format each pivot table to pick up all the rows and adjust to the correct number of pages once the page field changes. Here is what I have pieced together, it does nothing at this point, can someone help me or point me to the answer please:
=============================================
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
Application.EnableEvents = False
Select Case ActiveSheet.Name
Case "Vol by Customer"
Dim LR As Long
LR = Cells(Rows.Count, "D").End(xlUp).Row
Dim myRange As String
myRange = Range("$A$1:D" & LR).Address
With ActiveSheet
.PageSetup.PrintArea = myRange
.PrintOut
End With
Case Else: ActiveSheet.PrintOut
End Select
Application.EnableEvents = True
End Sub
================================
consider me "frustrated" :pray:
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Hi,

I'm not clear on the question. If you reset the print area (ALT-F-T-S) then I think any time you go to print the range will automatically be OK. (I admit I haven't tested this.) Is that OK for you?

regards, F
 

Watch MrExcel Video

Forum statistics

Threads
1,122,333
Messages
5,595,560
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top