Print Specific Charts

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
0
Petrov (4)
7/9/2007 7:28:01 PM
excel.charting 18370 articles. 0 followers. Follow

5 Replies
304 Views

Similar Articles

[PageSpeed] 47

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
0
7/9/2007 9:56:00 PM
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
0
Petrov (4)
7/9/2007 10:06:18 PM
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.

0
Petrov (4)
7/9/2007 10:42:01 PM
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.
> 
0
7/10/2007 3:24:01 AM
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

0
Petrov (4)
7/10/2007 2:20:04 PM
Reply:

Similar Artilces:

Fixed printing page breaks
Hello I am trying to set fixed page breaks (excel 97) I have set the print area (3 pages) I have set the fitting (3 pages) I have put pages breaks (2) The printing is fine on my printer but it changes the page breaks when printed from other printers (ie: start of page 2 appear at the bottom of page 1) please help, I am dying... thanks ben When you set the print to fit option it calculates the reduction in page size. Change your selection after your print to fit back to the "adjust to" option and you page breaks will work. Lance >-----Original Message----- >Hello &...

How to refer to cells dependent on a specific variable
I'm making a contact list where I in sheet 2 want to take out specifi details from sheet 1 dependent on whether there is arranged a meetin with the company. In sheet 1 each company has a row and when i tick of "Meeting" from a validation list, I have put under contact result fo each company, I want a function that can take the relevant inf (company name, contact person, date of contact, etc) and put them i sheet 2. Do you have a suggestion? -- Krelle ----------------------------------------------------------------------- Kreller's Profile: http://www.excelforum.com/member.p...

Printing Issues when distributing a form to multiple users
I am distributing a spreadsheet to multiple users. The spreadsheet is being used as a form for data entry. The problem i am having is that when it is being printed, the users are getting varying page layouts where the page breaks have changed, due the changes to the length of the document growing from the free text they are entering. The page breaks that excel chooses don't seem to be spaced well, leading to one or two of the pages being very short (i.e., leaving a lot of free white space), whereas the other pages are taking up the entire page. Is there a particular page layout selection...

cant print spread sheet with lines on it
i am trying to put some info onto excel but when i print it out i only get the print and not the grid lines can somebody tell me the correct settings thanks. File > Page Setup > Sheet tab > Put a checkmark in 'Gridlines'. Hope this helps. George Gee "steve earle" <steve earle@discussions.microsoft.com> wrote in message news:6017023B-592D-400B-A671-8B65C1BB02A4@microsoft.com... >i am trying to put some info onto excel but when i print it out i only get > the print and not the grid lines can somebody tell me the correct settings > thanks. t...

Where free 2 sigma statistical process control chart, free?
where can I download a free statistical process control chart template? ...

set a reminder to print or edit in an excel document
Is there a way to set a reminder to print part of a workbook or edit a workbook on a certain day. I need to print and edit a workbook on a certain day at a certain time, but I can never remember to do this. Is there any way of setting a reminder alarm or notification. Thanks > Is there a way to set a reminder to print part of a workbook or edit a > workbook on a certain day. ... If you have Excel, you might be using Outlook also. Both are part of Office 2003. Outlook has a "calendar" feature that pops up reminders the user can schedule. I use it all the ti...

How do I print a list of my E-mail folders?
I need to print a list of my e-mail folders. "AHD23" <AHD23@discussions.microsoft.com> wrote in message news:5BFD12CA-5D55-4376-AF9C-58703042577D@microsoft.com... >I need to print a list of my e-mail folders. I don't believe there's a way to do that, except as a screen shot. -- Brian Tillman [MVP-Outlook] This might be a workaround for you: http://www.vboffice.net/sample.html?mnu=2&smp=12&cmd=showitem&lang=en In example #1, replace the line LoopItems Folder.Items by Debug.Print Folder.Name After running the code,...

Hyperlink from HTML to a specific Excel workshhet
We have html files with links in them that open an Excel document and goes to a specific worksheet. Since upgrading from Office 2003 to Office 2007 we now find that the link will only open the workbook to its last saved active worksheet and not the worksheet required. Does anyone know how to get around this? HTML Syntax is <a href="c:\temp\book1.xlsx#Sheet3!A1"> LableName </a> Using 2007 I created a tiny workbook called master.xlsx The only thing in master is a hyperlink to another workbook, specific sheet, specific cell. I saved master as html. ...

Constant List
Is there an easy way to return the values of the ChartType constants? Excel 97 does not seem to list them in help and I have not been able to find them in google.group searches. Thanks M Select xlChartTypes in the Classes pane of the object browser. The constants are named in the members panel. If you select one, it provides the numeric equivalent at the bottom of the object browser, e.g., Const xlArea = 1 Member of Excel.XlChartType - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ M wrote: > Is there...

Excel --- Physical dimensions of a chart object
Is there a way to know the physical dimensions of a chart object on a worksheet? I have data in several worksheets and I would like all my charts to be of the same size (obviously this means that data with a smaller range of values will be more spread out than data with a larger range of values). Thanks, Nimmi This is just an example: Sub Macro2() ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select n = ActiveSheet.Shapes("Chart 1").Width m = ActiveSheet.Shapes("Chart 1").Height MsgBox (n & " BY " & m...

Controlling Gap Width for BAR Chart
Hi, I have the following data for example. A = 2 B= 3 C = 4 I can create a 2 D Bar chart from this quite easily. Now If I make the data to A = 2 , 3 B= 3 , 4 C = 4 , 5 I do want to plot the data on the basis of first two values as mentioned in the first example, but I want to control the width of the Bar from the value given after comma. Please help. On Sat, 24 Feb 2007, in microsoft.public.excel.charting, Fazi <farazahmedtoor@gmail.com> said: >I have the following data for example. >A = 2 , 3 >B= 3 , 4 >C = 4 , 5 > >I want to plot the data on the basis of firs...

How do I add y-axis labeling on the right side of chart?
I'd like to add an axis with same values on the right side of the chart. You'll need to have at least two series in order to enter a secondary axis. On one of the series, change the Axis to Secondary axis. "DavidNHFCU" <DavidNHFCU@discussions.microsoft.com> wrote in message news:28759EE0-759E-4964-8F5E-6C49099653B6@microsoft.com... > I'd like to add an axis with same values on the right side of the chart. How can I force the y-axis at the bottom of my chart to appear for all 12 months when I only have Jan, Feb and March figures to put in the first thre...

excel chart showing movable graph at what point we make a profit
in a business would like to make a chart based on accumulated Earnings & Profit/Loss for year to date, perhaps movable showing our break even point and by moving chart to a another earnings figure what profit we should make. For example put in earnings for YTD Profit/Loss YTD then I can move the earnings part of the chart to show what our profit would be for a given earnings figure. Alternatively If For example if we earn $200 for year so far and profit is $0 but any amount over that is 18% profit so I can move chart line to say $240 and it will say what profit I will make. Hope ...

Bar Chart (2D) won't let me 'gap' one bar behind the other?
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I have a nice, functioning bar chart that has 2 data fields: ACTUAL and BUDGET. <br><br>Actual is on one axis and Budget is on the 2nd axis. <br><br>These charts look nice side by side but I want one bar to be partly behind the other. <br><br>So, I select a data set. <br> Choose Format Data Series <br> Options <br> Overlap... (and no matter what value I choose, I can't overlap)???? <br><br>But, if I can adjust the Gap Width. <br><...

What is the best chart for this....
When I want to compare how each salesperson's sales contributes to Total Sales, I can use a pie chart. But what do I use when I want to compare how each salesperson performed against the *average* of total sales? I am envisioning a vertical bar chart with a horizontal line at the average, but I need a solution that can be generated by a macro. Any advice? Thanks in advance. Patti Patti - That's how you would do it, a column chart with a horizontal line. On this web page, I give a couple techniques to achieve the horizontal line: http://peltiertech.com/Excel/Charts/AddLine...

"Print to Scale" Excel 2007 Charts
In Excel 2003, one could double-click a chart, print, and the chart would auto-size to fill a page. I am unable to print my newly-created Excel 2007 charts so they auto-size to print one-to-a-page (now requires several time-consuming steps for reformatting titles, axes, etc.). I do not have a "print to scale" option in my page setup. Printing to a Ricoh Aficio AP610N. Thanks so much. I will assume we are talking of an embedded chart (one sitting on a worksheet) and not a chart sheet. In Excel 2003 (all earlier) you did not double click, you just single clicked to...

Stacked and single column in same chart?
How can I do a chart with a stacked column beside a single column? When I build a stacked column chart, any new source data I add wants to put it in the same stacked column. Use one of the links on this page. You need to set up the data so the single column is in a stacking position with no other columns of data. http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Einstine wrote: > How can I do a chart with a stacked column beside a sing...

How do I print labels after I have mailmerge on excel
?I saved my addresses in Excel. I want to print them on labels How do I do that? Also how can I get a picture on the labels? Bika, do a mail merge with word. For help on Word mail merge using Excel as the data source have a look here http://www.mvps.org/dmcritchie/excel/mailmerg.htm http://www.mvps.org/word/FAQs/MailMerge/CreateAMailMerge.htm http://www.mvps.org/word/FAQs/MailMerge/CreateADataSource.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Ex...

Print multiple copies per sheet
I have a pubisher file that is a card with a size of 5 x 6 and is 2 pages. Is it possible to print 2 copies of this file on a letter size page so that there will be 2 double sided copies on the letter size page. I need to laminate these cards so if I could get 2 per page I would just need to cut the page in half. I am using Publisher 2003. Thanks in advance for any help. Best regards, Dee Page setup, Business card, change copies per sheet, Side margin 1.25, top margin ..5, zero gaps, okay out, in the next screen change the measurements to width 6" height 5". The above will...

Dbl sided printing problem
I am making a "Program" for a Barbershop Chorus concert using MS Publisher in the "Catalog" mode (which prints both sides of course.) I cannot seem to find out what kind of paper I can get (short of photo imaging paper) that is opaque enuff to prevent the backside of a page from showing through the "white areas" of the front side. It's not a bleed-through, it's just that the paper is to darn translucent. I am using 24 lb inkjet paper with an inkjet printer. I am sure this is anything but a new problem, but I have not found anything in the Goog...

Publisher 2000 print problem
What we see in the working document and what goes to print is different. Is there a way to enter the print preveiw document to fix it at that point. There seems to be no way to resolve this issue??? Please help if you can. ...

re-print an RTV packing slip
There should be a way to re-print an RTV packing slip after the RTV shipping has been processed. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=8f250615-702c-4e00-8f24-f90...

How do you print the last page of an 8 page banner in Publisher?
When I make a large banner, I would like to select a particular page to print. The print preview option allows selection of pages 1,2, etc but does not allow for example only the last page. Print to a PDF... http://www.primopdf.com/ -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "FredSC" <FredSC@discussions.microsoft.com> wrote in message news:78386D22-8473-4EC8-A032-82F802A26C59@microsoft.com... > When I make a large banner, I would like to select a particular page to > print. The print preview option allow...

Can anyone help with this chart?
I would like to have the following values/labels on the y and x axis of any style of chart: X-axis: July, August, September etc for a full financial year; Y-axis: FB, AP, DR, LA, PC. OR the other way around. Presently the Y-axis simply displays the labels 'Jan Jan Jan Jan Jan' etc. Thank you. Any help would be much appreciated. Check out Jon Peltier's article on creating a vertical category axis: http://peltiertech.com/Excel/Charts/Y_CategoryAxis.html -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "A little stuck" wro...

Supplier on Printed PO Receipt
Can someone tell me how to make the supplier name or Supplier code print on the Purchase Order Receipt. Ask your RMS partner to show you the available templates in Partner Source. There's a PO tempplate there that includes your account number in the address field. -- Repetition is the key to success. Practice, practice, practice! and Equip Yourself at http://www.aluminumbats.com "Mark" wrote: > Can someone tell me how to make the supplier name or Supplier code print on > the Purchase Order Receipt. ...