VBA program to adjust Print Area on ever changing Pivot Table

[jonlisa11]

Hi Team, I built some pivots for a friend that enables him to filter by different manufactures / locations, etc. to obtain different result that he needs for ordering supplies, shipping product to, etc. However, each time that the pivot is adjusted, i.e. by filtering, the print area needs to be adjusted. I am trying to have this file as user friendly as possible so the user does not need to go in and adjust the print area every-time he adjusts the pivot. Can someone help me in setting up a macro "Print Button" on each tab that will auto adjust the print area and print?

I have included the file below

Thanks in Advance -

[jaslake]

Hi jonlisa11

Welcome to the Forum!

You can try this Code...let me know of issues...

Please Login or Register  to view this content.

To Print the Worksheets change these lines of Code from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[jonlisa11]

Hi Jaslake, I am not sure I am copying the code and inserting in my file correctly. Here is the file with your code inserted - i am really new at this VBA stuff.

Thank you for the response -

[fredlo2010]

Hello jonlisa,

See if this code works.

Place the following code in a module in the workbook

Please Login or Register  to view this content.

Now under the "ThisWorkbook" module place this one

Please Login or Register  to view this content.

That's it you don't have to do anything else just print normally. Every time you print the code will be activated.

I have attached a sample so you know where the codes are supposed to go. Attachment 262458

Thanks

[Andy Pope]

If you hook into the SheetPivotTableUpdate event of the Thisworkbook object you can set the print area when the table updates and the user can use the built in print function, so no need for any extra button to press.

Please Login or Register  to view this content.

biggest issue is deciding how each PT should be printed. All on one page or 1 page wide etc.

[jaslake]

Hi jonlisa11

Attached is your original Sample File with the Code embedded. I've assigned CTRL + x as a keyboard shortcut.

[jonlisa11]

Hi Jaslake, I copied your code into my working Excel file. but for some reason i cannot get your CTRL+X option to work. I can only run it by clicking on run macro, what am i missing?

Thanks
JL

[jaslake]

Hi jonlisa11

You need to assign the Shortcut to that Macro. Press alt + F8. Select Print_Pivots. Select Options. Place an x in the Box next to CTRL +. Select OK.