Printing "grouped" pie charts on 1 single page

[Vegiepie2016]

Hi there

I have 6 set pie charts embedded on a worksheet (grouped them together).

Code has been set up to print each chart on a separate sheet, but was wondering how I could print all 6 grouped pie charts on the 1 landscape page?

Existing code (loops through each pie / print previews but on individual pages)

Please Login or Register  to view this content.

[Kevin#]

One suggestion:
Set out your charts as you want to see them printed out and then set the print range to suit.
So you are printing the worksheet, not the charts
Something like:

Please Login or Register  to view this content.

[Vegiepie2016]

Thanks Kevin, but it is only the charts which are needed, not the entire sheet as this has long tables etc on it as well.

In addition to this, the charts position might not always be the same (as columns get added to the table and the charts get moved) - so I am not sure that the above code will cater for a potentially moving print area.

Not sure if there is a way to loop through the chart objects, move them to a temp chart sheet, print and then remove temp?

Just very new at coding - trying to find ways to automate things at present. The 6 pie charts print fine individually (each on a separate page), but would like to have them printed all on the one page for better overview and comparison ...

[Kevin#]

I will look at your "ideal solution" later but how about creating a second sheet with a copy of the same charts on it? Wouldn't that work?

[Vegiepie2016]

re: 2nd sheet Long story, but the charts sit in a hidden "template" sheet, which may or may not be activated. THe workbook already contains about 20 sheets, so I am trying to avoid adding another for the sake of printing.

At least it will print a copy of just the pie chart, but either using lots of ink for the large pie or either a tiny little pie on a whole sheet, hence I was trying to print the group of 6 on the sheet together (set up as 2 rows of 3)

[Kevin#]

Try this. See if it works for you.
Then we will turn the additional sheet into a temporary sheet that gets created at time of printing and then deleted again thus avoiding increasing the size of the workbook

- Create a sheet named "PrintCharts" (no space!)
- amend line Set wsONE = Sheets("OriginalSheet") and put in your current sheet name where the charts reside now
- amend the "Set the dimensions and spaces" numbers to make it look the way you want
- I have put in a message box so that you can see where each chart will land (keep hitting ok). Delete that when done.
- Paste the amended macro when you reply

I will then amend to include the temporary worksheet creation & deletion etc

Please Login or Register  to view this content.

[Vegiepie2016]

Hi Kevin - Thanks for looking into this so far -- didn't mean for you to come up with miracles! The issue I have with your code so far is that the 6 pie "original" sheet is a template, which gets renamed every time the user creates a new worksheet based on this template. That is where I constantly get stuck as charts and sheets will have a name I won't be able to anticipate as they are user-defined at the time of creation.

If there is no easy solution, then I will just stick to the printing 1 pie chart / sheet. It does have the print preview option in my code, so if the pie is blank, the option is there not to print it.

[Kevin#]

That should not be a problem
How does the template get renamed?
Which macro does the template naming
Can you paste "the line in the macro that creates the template sheet" into your next reply
Also the lines that capture the user's input

[Vegiepie2016]

The user will be prompted via msgBox

Code:

Please Login or Register  to view this content.

[Kevin#]

This should now be the finished product.
We need somewhere to store the "name of the template" in between macros (choose a cell in an existing sheet that won't be amended/affected after macro RenameSheetErrorTrap runs)
A temporary sheet is created named TemporaryPrintCharts"
(need to add instruction to delete that temporary sheet after printing)


What you need to do
in macro RenameSheetErrorTrap
add this line where I have indicated (below) with the XXXXXXX
(amend it to reflect where you want to store the template sheet name)

Please Login or Register  to view this content.

BEFORE replacing current macro LineUpMyCharts with the one below, do not forget to amend the dimensions with the ones that you have already checked work for you.

replace macro LineUpMyCharts with the one included below and
amend this line (as for above)

Please Login or Register  to view this content.

At the end of the print macro add these lines to delete the temporary sheet

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.