Printing multiple embedded charts and worksheets 2

[Welshbird]

I have a spreadsheet I populate monthly with latest sales figures, and I then provide this both electronically and printed to the managers.

I have three worksheets each containing 10 or more charts, and two worksheets that I print which just contain data.

Is there an easy way to try and print these in one go? Even just to print all of the charts of one worksheet at a time would speed this up. I tried recording a macro, but when I ran it to test it fell over in a heap.... :-(

Macro looked like this:

Sub TestPrint()
'
' TestPrint Macro
' Macro recorded 07/09/2010 by Welshbird
'

'
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    ActiveWindow.Visible = False
    Windows("MMP_201008.xls").Activate
    ActiveSheet.ChartObjects("IRE 2010").Activate
    ActiveChart.ChartArea.Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    ActiveWindow.Visible = False
    Windows("MMP_201008.xls").Activate
    ActiveSheet.ChartObjects("UK & IRE 2010").Activate
    ActiveChart.ChartArea.Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub

Cheers chaps,

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Scott24x7]

Welshbird,
Very easy way. Just CTRL+Click each tab you want to print. Go to "Print", and then select "Active Sheets". Click Print, and it will just print the tabs you have selected, and the content that you have defined in the "Printable Range" for each of them.
If you have more data on the sheet then you want to print, go to Page Setup, select Printable Range (select the area you want to have INSIDE the Printable Area) or, you can do this visually through the "View" "Page Break" preview and drag the heavy blue line to only include areas you want to print.
Then, as above, select the tabs you want, and click print (with "Active Sheets" selected.
If you have page numbers set at the bottom of the sheets, it will even number them in sequence (i.e. 1 - 22 across worksheets.)

Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[Welshbird]

thanks - That's what I have done for the sheets with data that I want to print.

The difficult and time-consuming bit is the sheets with 10 or more charts on each one, as I need each chart printed on a separate sheet. I have clicked each chart, and then crtl p to print, and that works fine, so the macro I recorded was just trying to do that for all ten charts on one sheet, but it doesn't work, so the really issue is to print multiple embedded charts on separate sheets each, Any ideas?

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[SkipVought]

hi,

To print multiple charts in a sheet individually...

dim co as chartobject

for each co in activesheet.chartobjects
  co.chart.printout
next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlhelp]

You will probably need to extend Skip's code to:

For Each ws In ActiveWorkbook
For Each co in ws
co.chart.printout
next co
next ws

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[SkipVought]

rather...

dim ws as worksheet, co as chartobject

For Each ws In ActiveWorkbook[b].worksheets[/b]
  For Each co in ws[b].chartobjects[/b]
    co.chart.printout
  next co
next ws

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I know. Cerebral flatualtion. ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Welshbird]

Thanks guys!

You're all fabber than a fab thing on fab day...

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen