I have a little problem with some code I've been using. In my workbook I have
7 sheets with 45+ pre-made charts. Some have data but most don't. The current
code I have counts how many charts there are on the sheet, selects,
activates, and prints them all the way I like. Thing is I'd like it to print
only the charts with data in them.

I thought that a code could be made that checks the source range of the
chart for numbers then prints that chart. However because there are 45+
charts and 7 sheets that would take awhile.

Below is an example of the code i'm currently using.

Sub PrintEmbeddedCharts()
Dim ChartList As Integer
Dim X As Integer
' Variable chartlist stores a count of all embedded charts.
ChartList = ActiveSheet.ChartObjects.Count
' Increments the counter variable 'X' in a loop.
For X = 1 To ChartList
' Selects the chart object.
ActiveSheet.ChartObjects(X).Select
' Makes chart active.
ActiveSheet.ChartObjects(X).Activate
' Prints one copy of active chart.
ActiveChart.PrintOut Copies:=1
Next
End Sub

The following routine should loop through each embedded chart in your
workbook and print only those charts with values other than zero:

Sub PrintChartsWithValues()

Application.ScreenUpdating = False

Dim hSheet As Worksheet
Dim Ws As Worksheet
Dim Cht As ChartObject
Dim Srs As Series
Dim Pt As Points
Dim nPts As Long
Dim iPt As Long

Set hSheet = ActiveSheet

For Each Ws In Worksheets
For Each Cht In Ws.ChartObjects
Y = 0
For Each Srs In Cht.Chart.SeriesCollection
With Srs
nPts = .Points.Count
For iPt = 1 To nPts
X = Srs.Values(iPt)
Y = X + Y
Next
End With
Next Srs
If Y < 0 Then Cht.Chart.PrintOut Copies:=1
Next Cht
Next Ws

hSheet.Select
Range("A1").Select

End Sub

--
John Mansfield
http://cellmatrix.net





"Petrov" wrote:

I have a little problem with some code I've been using. In my workbook I have
7 sheets with 45+ pre-made charts. Some have data but most don't. The current
code I have counts how many charts there are on the sheet, selects,
activates, and prints them all the way I like. Thing is I'd like it to print
only the charts with data in them.

I thought that a code could be made that checks the source range of the
chart for numbers then prints that chart. However because there are 45+
charts and 7 sheets that would take awhile.

Below is an example of the code i'm currently using.

Sub PrintEmbeddedCharts()
Dim ChartList As Integer
Dim X As Integer
' Variable chartlist stores a count of all embedded charts.
ChartList = ActiveSheet.ChartObjects.Count
' Increments the counter variable 'X' in a loop.
For X = 1 To ChartList
' Selects the chart object.
ActiveSheet.ChartObjects(X).Select
' Makes chart active.
ActiveSheet.ChartObjects(X).Activate
' Prints one copy of active chart.
ActiveChart.PrintOut Copies:=1
Next
End Sub

Thxs for replying so quickly. The code works great but I was wondering if you
could tell how to alter the code to only print a certain from a certain
sheet. I currently have a User Form created so that charts if you click a
check-box and press print it, it prints 1 of 7 departments named Dept. 1,
Dept. 2, etc.

"John Mansfield" wrote:

The following routine should loop through each embedded chart in your
workbook and print only those charts with values other than zero:

Sub PrintChartsWithValues()

Application.ScreenUpdating = False

Dim hSheet As Worksheet
Dim Ws As Worksheet
Dim Cht As ChartObject
Dim Srs As Series
Dim Pt As Points
Dim nPts As Long
Dim iPt As Long

Set hSheet = ActiveSheet

For Each Ws In Worksheets
For Each Cht In Ws.ChartObjects
Y = 0
For Each Srs In Cht.Chart.SeriesCollection
With Srs
nPts = .Points.Count
For iPt = 1 To nPts
X = Srs.Values(iPt)
Y = X + Y
Next
End With
Next Srs
If Y < 0 Then Cht.Chart.PrintOut Copies:=1
Next Cht
Next Ws

hSheet.Select
Range("A1").Select

End Sub

--
John Mansfield
http://cellmatrix.net





"Petrov" wrote:

I have a little problem with some code I've been using. In my workbook I have
7 sheets with 45+ pre-made charts. Some have data but most don't. The current
code I have counts how many charts there are on the sheet, selects,
activates, and prints them all the way I like. Thing is I'd like it to print
only the charts with data in them.

I thought that a code could be made that checks the source range of the
chart for numbers then prints that chart. However because there are 45+
charts and 7 sheets that would take awhile.

Below is an example of the code i'm currently using.

Sub PrintEmbeddedCharts()
Dim ChartList As Integer
Dim X As Integer
' Variable chartlist stores a count of all embedded charts.
ChartList = ActiveSheet.ChartObjects.Count
' Increments the counter variable 'X' in a loop.
For X = 1 To ChartList
' Selects the chart object.
ActiveSheet.ChartObjects(X).Select
' Makes chart active.
ActiveSheet.ChartObjects(X).Activate
' Prints one copy of active chart.
ActiveChart.PrintOut Copies:=1
Next
End Sub

I'm sorry I also failed to mention I have TWO series in my graphs. Series 2
which will always have a number in it and Series 1 which will not (unless
there was data entered). I also apologize for not thoroughly proof-reading my
last post.

"Petrov" wrote:

Thxs for replying so quickly. The code works great but I was wondering if you
could tell how to alter the code to only print a certain from a certain
sheet. I currently have a User Form created so that charts if you click a
check-box and press print it, it prints 1 of 7 departments named Dept. 1,
Dept. 2, etc.

If I'm understanding correctly, given a userform and given that you want to
only print specific charts from a specific department, a listbox might have
to be added to the userform that returns all of the charts specific to a
certain department and allows the user to pick and print the chart/charts of
your choice. That would involve quite a bit more userform design and coding.
If you wanted to email the workbook to me I could take a look at it when I
can ( john at cellmatrix dot net).

--
John Mansfield
http://cellmatrix.net





"Petrov" wrote:

I'm sorry I also failed to mention I have TWO series in my graphs. Series 2
which will always have a number in it and Series 1 which will not (unless
there was data entered). I also apologize for not thoroughly proof-reading my
last post.

"Petrov" wrote:

Thxs for replying so quickly. The code works great but I was wondering if you
could tell how to alter the code to only print a certain from a certain
sheet. I currently have a User Form created so that charts if you click a
check-box and press print it, it prints 1 of 7 departments named Dept. 1,
Dept. 2, etc.

That is correct John. I've been playing around with a listbox UserForm and I
found it quite difficult to get one to work due to my limited knowledge of
VBA. I'll send you an example of the sheet I have along with two Userforms.
Userform 1 will be what I am currently using and User form 2 is what I've
been trying to get to work. I didn't write any of the code for the listbox.
It was an example I found on another forum. Thank you for your help. It is
greatly appreciated!

"John Mansfield" wrote:

If I'm understanding correctly, given a userform and given that you want to
only print specific charts from a specific department, a listbox might have
to be added to the userform that returns all of the charts specific to a
certain department and allows the user to pick and print the chart/charts of
your choice. That would involve quite a bit more userform design and coding.
If you wanted to email the workbook to me I could take a look at it when I
can ( john at cellmatrix dot net).

--
John Mansfield
http://cellmatrix.net