VBA Copy Chart as Picture

Given the procedure below, is there a way to copy the chart as a picture to 
the range without selecting the range each time?  If possible I would like to 
speed up the macro by not selecting a range.

Sub LoopThroughCharts()

    Dim Rng As Range
    Dim Cht As Chart

    Set Cht = Sheets("MC").ChartObjects("Cht1").Chart
    
    For Each Rng In Sheets("Dash").Range("F4:F11")

        Cht.CopyPicture
              
        Rng.Select
        ActiveSheet.Paste

    Next Rng
 
End Sub
 
Thanks for your help.

-- 
Steph
0
StepH (24)
1/6/2009 4:01:00 AM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
594 Views

Similar Articles

[PageSpeed] 32

Hi,

Something like this.

Sub LoopThroughCharts()

    Dim Rng As Range
    Dim Cht As Chart

    Set Cht = Sheets("MC").ChartObjects("Cht1").Chart

    For Each Rng In Sheets("Dash").Range("F4:F11")

        Cht.CopyPicture
        With Rng.Parent
            .Paste
            With .Shapes(.Shapes.Count)
                .LockAspectRatio = msoFalse
                .Left = Rng.Left
                .Top = Rng.Top
                .Width = Rng.Width
                .Height = Rng.Height
            End With
        End With
    Next Rng

End Sub

It sizes the chart to the cell. If you don't want that comment out the 
changes to Width,Height and LockAspectRatio

Cheers
Andy
-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Steph" <Steph@discussions.microsoft.com> wrote in message 
news:D99104FA-37BE-477E-9D57-74C9B7EE5C4A@microsoft.com...
> Given the procedure below, is there a way to copy the chart as a picture 
> to
> the range without selecting the range each time?  If possible I would like 
> to
> speed up the macro by not selecting a range.
>
> Sub LoopThroughCharts()
>
>    Dim Rng As Range
>    Dim Cht As Chart
>
>    Set Cht = Sheets("MC").ChartObjects("Cht1").Chart
>
>    For Each Rng In Sheets("Dash").Range("F4:F11")
>
>        Cht.CopyPicture
>
>        Rng.Select
>        ActiveSheet.Paste
>
>    Next Rng
>
> End Sub
>
> Thanks for your help.
>
> -- 
> Steph 

0
andy9699 (3616)
1/6/2009 9:16:23 AM
Andy,

This is perfect.  Thank you so much!

-- 
Steph


"Andy Pope" wrote:

> Hi,
> 
> Something like this.
> 
> Sub LoopThroughCharts()
> 
>     Dim Rng As Range
>     Dim Cht As Chart
> 
>     Set Cht = Sheets("MC").ChartObjects("Cht1").Chart
> 
>     For Each Rng In Sheets("Dash").Range("F4:F11")
> 
>         Cht.CopyPicture
>         With Rng.Parent
>             .Paste
>             With .Shapes(.Shapes.Count)
>                 .LockAspectRatio = msoFalse
>                 .Left = Rng.Left
>                 .Top = Rng.Top
>                 .Width = Rng.Width
>                 .Height = Rng.Height
>             End With
>         End With
>     Next Rng
> 
> End Sub
> 
> It sizes the chart to the cell. If you don't want that comment out the 
> changes to Width,Height and LockAspectRatio
> 
> Cheers
> Andy
> -- 
> 
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info
> "Steph" <Steph@discussions.microsoft.com> wrote in message 
> news:D99104FA-37BE-477E-9D57-74C9B7EE5C4A@microsoft.com...
> > Given the procedure below, is there a way to copy the chart as a picture 
> > to
> > the range without selecting the range each time?  If possible I would like 
> > to
> > speed up the macro by not selecting a range.
> >
> > Sub LoopThroughCharts()
> >
> >    Dim Rng As Range
> >    Dim Cht As Chart
> >
> >    Set Cht = Sheets("MC").ChartObjects("Cht1").Chart
> >
> >    For Each Rng In Sheets("Dash").Range("F4:F11")
> >
> >        Cht.CopyPicture
> >
> >        Rng.Select
> >        ActiveSheet.Paste
> >
> >    Next Rng
> >
> > End Sub
> >
> > Thanks for your help.
> >
> > -- 
> > Steph 
> 
> 
0
StepH (24)
1/6/2009 12:03:01 PM
Reply:

Similar Artilces:

Copy whole table into one cell
Office Excel 2003 on Windows XP I'm trying to create a report-maker with excel... ugh! Anyways, there is a template that users are filling out saying whether something is defected or not... that's not important, the important part is, that I'm having VBA go though the table (using a button after it is filled out) and deleting the rows that aren't defected... in other words, if there are blank cells in column C (for example), the whole row in which that blank cell is located is deleted. I'll never have more than 40 rows and 5 columns in the table so I don'...

copy header/footer btwn sheets
How do I copy the header/footer from one spreadsheet to another? Copying the entire worksheet and using Edit->Paste Special->Formats leaves out the Header/Footer. and I know how to craete a copy of an existing sheet, but it's a pain to then delete the contents from Sheet1Copy, copy contents from Sheet2 to Sheet1Copy, delete Sheet2, then rename Sheet1Copy to Sheet2. I swear I saw somenoe do this years ago with a combination of holding down a key or keys and clicking on sheet and dragging to another, but I can NOT remember how to do it. Trevor, group the worksheets, then do the he...

Minimize a Pane via VBA
I know that the following statement can be used to hide or show a panel, but how would you minimize it? The article below is most certainly a genuine Microsoft article as is obviously omits the relevant information. objExplorer.ShowPane 4, False If Visible=True/False is all you can do, does the article then contains all the relevant information? -- Best regards Michael Bauer - MVP Outlook : Outlook Categories? Category Manager Is Your Tool : VBOffice Reporter for Data Analysis & Reporting : <http://www.vboffice.net/product.html?pub=6&lang=en&g...

How to do auto save in Excel VBA?
Hi, How can I do auto save in Excel VBA, say every 2 minutes? Thnak you. GL Are you trying to control the Autosave addin via VBA or trying to write your own save program? If you just want that functionality... What version of excel are you using? If you're using xl2k or lower, you can install the AutoSave addin (you may need the CD if you didn't install it previously). If you're using xl2002 or higher, then AutoSave was replaced with AutoRecovery (mostly used if excel/windows crash). It's not the same as autoSave, though. If you still have your old version of autosave....

Excel Charts blurred when rendered to web
Hi. I am publishing a larhge number of graphs to a web page from XL2000 using the save as web page functionality. Some of the graphs are comming through as soft / blurred gifs. This appears to be random, as different charts are affected each month. Has anyone got any ideas of where to look for the solution?? I have played around with the Resolutions, pixel setting etc. I am currently using 1024x768 and 120ppi many thanks Murray Murray - The fuzzy images aren't JPGs, are they? JPGs can have a degraded appearance near sharp color transitions. Are the pictures displayed i...

Charting/ Graph question
Hi all I'd like to visually display a table that contains the following table structure StaffName Software 1 Skill Level Software 2 Skill Level (10 software titles >) (around 50-60 people) What i'd like to is output a simple visual output that shows me how many people are at a particular level for the each software (but all on one chart). I imagined that it may be nice to see blobs based on the size of the number of staff at a particular skill level for the specific software. However i am not sure if this is the right way to represent this data. I've created a se...

Can VBA codes can be protected from view.
Yes but it can be cracked if someone is determined enough, have a hex editor and can go online If you go into the VBE (alt + F11) you can right click any VBA project and select properties (or select the project and do tools>vba project properties), there look under protection -- Regards, Peo Sjoblom "Prakash" <Prakash@discussions.microsoft.com> wrote in message news:9FCC2DB1-BB7B-4DC0-8145-6D719556581D@microsoft.com... > Yes, select the project in the VBIDE, right-click and choose Properties, and it is all on the Protection tab. -- HTH Bob Phillips (rem...

Too many records for chart
Why does access limit the number of data in can show on a graph (chart)? Or, how can I plot less data points (less records) so a complete representation of data will show on graph. Using Access 2000. -- FL On Tue, 4 Dec 2007 15:13:02 -0800, FL <FL@discussions.microsoft.com> wrote: You didn't tell us how many data points you're talking about but as a thought experiment let's say you have 100,000 y values, one for each sequential x-value. Let's graph them. Your screen is about 1280 * 1024 pixels. So you'll have about 78 data points on each pixel in the x-directio...

link chart title?
I've learned about dynamic charts in this forum which has been very helpful in autmating some charts that I use. I would also like the chart title be linked in some way to a cell. Is that possible? Hi, Here are a couple of explanations. http://tushar-mehta.com/excel/newsgroups/dynamic_chart_title/fp_tutorial.html http://www.andypope.info/tips/tip001.htm Cheers Andy Dave Breitenbach wrote: > I've learned about dynamic charts in this forum which has been very helpful > in autmating some charts that I use. I would also like the chart title be > linked in some way to a...

Combination chart - Line
Hello! I am trying to create a combination chart that has bars and a line. The bars should all relate to one axis while the line should relate to the secondary axis. The problem is that when I make the chart, it relegates one of my bars to a line. I can change the axis to the primary where it should be but it is a line, not a bar. I have also tried making a column only chart and add in my line and secondary axis but to no avail. As soon as I put in the secondary axis, Excel converts one of my bars to a line. Any input would be a great help! Thanks, Debbie Hi, Make you own com...

bell curve charts
Hello all, I'd like to know how to make a bell curve chart, which when given a single monetry amount and a time line, can disperse the amount over the specified time line in a bell curve. Any help would be much appreciated. :) A wrote on Tue, 1 Sep 2009 19:15:01 -0700: > I'd like to know how to make a bell curve chart, which when > given a single monetry amount and a time line, can disperse > the amount over the specified time line in a bell curve. I don't think you are providing sufficient information or perhaps you should try again to say what you want. A nor...

Dynamic Offsetting, Names, Arrays, Range, Chart Values. NEED HELP
Hi, I would like to enter values into a Chart from a Named Array(Range). The Array should not be an actual Range, but a virtual array based on logic applied to a Range. In my case picking values in ColumnB based on values in ColumnA Ideally I woud like to write something like: Chartvalues:= OFFSET(ColumnA;INT(ColumnA/3);1) and then make a Chart out of it. (I actually want to do something more complex: a variable-width bar chart using an X/Y scatter, w/o using Bullen's method) My problem is that ONE offset is applied on the whole array, and not DYNAMICALLY to the different items. Need...

Charting with non contiguous columns
Trying to create a chart using information from columns that are not next to each other I need the information from Columns C, E and L. Rows 1-8 as an example Can that be done and if so how? Any help appreciated. David Hopper David, First, create a chart based on the data in column C. Once that chart is created, click on the chart (make sure it is active) and - going to the chart toolbar - select Chart -> Source Data. On the Series tab of the Source Data dialog box, select the "add" button. In the values selector (combination box) choose the range in column E. Once t...

column chart with 3 categories
I want to show 3 categories in a data table at the bottom of a column chart but only chart 2 of the categories in the diagram itself. can i do this? how? -- martymi Make the chart in normal way On the chart, right click a column of the 'unwanted' data series, use Format Data Series Format to No Line & No Fill Appearance is OK if the unwanted is not in the centre. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "martymi" <martymi@discussions.microsoft.com> wrote in message news:D192F0ED-DCC5-4E89-AF57-C94695DA897B@microso...

Receipt and rendered picture?
I know you can do it on reports, but is it possible to include the item picture on the register based on a hyperlink? I know I'm running the risk of slowing the whole thing down terribly too. I'm trying to use the picture to "support"/display another language. I'm admittedly not up to speed on XML and will likely be getting a book tonight... How about something like this? Entry.Item.PictureName or #concat("C:\Program Files\Microsoft Retail Management System\Store Operations\Pictures" _ Entry.Item.Picturename# <PICTURE> <FILENAME> E...

Excel 2003 - vba
HI All: I have this working in one workbook, but when I transplant it into another, it fails to function as expected. Issue: I would like to check my current workbook to ensure that a sheet does not exist, before trying to create it. In a UserForm, I allow the user to input a sheet name that they would like to import. Upon their depressing the "AddListBut" on the userform (ListForm) it checks to ensure the user put something in the Listbox "NewListName" then check to see if the worksheet already exists. Public ShtName as String Private Sub AddListBut_Click() If ListForm...

VBA syntax to SUM() at two cells below last data item
Using 2003 & 97 Need VBA syntax to SUM() at two cells below last data item. In the code below, I selected the cell using relative then absolute referencing ActiveCell.Offset(-3, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "=SUM(R[-32]C:R[-1]C)" Seeing the above, I am not sure of the syntax for VBA to find the cell two rows below the last data item; summing from Row 2 to the last data item and place an Excel formula in that cell. Thoughts? TIA Dennis Clarification: There are ten different worksheets that will have varying amounts of data every mo...

returned mail
--------------------------------------- There was a SMTP communication problem with the recipient's email server. Please contact your system administrator. <servername #5.5.0 smtp;552 MS-Office file containing VBA macros found inside of the email> --------------------------------------- I cannot figure out why this error continues to come up. This just started after loading the patches from last Tuesday, and it does not happen with all office files. These files probably do have macro's, but they had no problems sending before last Tuesday's patch day. A googl...

Help screen for VBA in A2007
I have been trying to use VBA in Access 2007. I need the help function often and find it non-existent in A2007-all they give you is some useless macro items. Is there a way to get the the great help screen that you could get in A2000? Or, is there some other place on the web where you can enter something very general and get a list of possible answers? Google! Seriously, I agree that MS did something to 2007 help. Google is almost every developers best friend regardless of their programming language. For crying out loud, Google is a better search tool for this very for...

Copy appearance but not conditional formatting
I have a spreadsheet with some conditional formatting. How can I copy the appearance of the cells without the formatting condition? Hi see your other post >-----Original Message----- >I have a spreadsheet with some conditional formatting. >How can I copy the appearance of the cells without the >formatting condition? >. > ...

Set up default chart
I always use the same chart type (XY), and am tired of changing the font size, background color, etc. every time I create a new chart. Is there a way to change the default settings for new charts? Thank you, Rob Rob wrote on Tue, 31 Jan 2006 16:06:18 -0400: R> I always use the same chart type (XY), and am tired of R> changing the font size, background color, etc. every time I R> create a new chart. Is there a way to change the default R> settings for new charts? R> Thank you, R> Rob You might want to look into templates. Try Help for a start. Templates are...

Copying data from Excel to Word
How can one copy the contents of cells in Excel to a Word document? Can you use a cut-and-paste method? Thanks, Bob on Jeff's computer yes -- Gary''s Student "Jeff Wright" wrote: > How can one copy the contents of cells in Excel to a Word document? Can you > use a cut-and-paste method? > > Thanks, > > Bob on Jeff's computer > > > ...

Conditional font in chart axis
How can I control the font in a chart (format axis, font strikethrough) with Visual Basic code. . I would like to switch the font on the values depending on the status of my calculations. I have a complex risk analysis that takes about 10 seconds to calculate on a 2 GHz machine. The calculation is only activated by pressing a button but I want to make sure that if the spreadsheet is not recalculated the values has a strikethrough. Thanks for the help, Urban Urban - Make yourself a dummy chart, with the text elements you'd like to reformat. Turn on the Macro Recorder, and make...

Getting Chart Point info
Hi - I am building a "drill down" chart. When you click on one of the points of the chart, it takes you to anohter chart based on the informaiton on the point. I am using the MouseUp event and I have the Arg1 and Arg2 data. What I don't have is the actual "name" of the point. If you mouse over the point it will tell you the "name" of the series and the "name" of the point. How do I get this "name"? Arg2 simply tells me which point (1,2,3,4 etc.) in the series. I need the name. thanks Lizzard ...

Linking Charts to publisher file saved as web page
Good Day I hope someone can help me with this question - I am using publisher 2007 to create a newsletter ( Saved as web page ) on our local intarnet. I am now trying to add / link charts and excel tables to the newsletter. I am only able to paste as picture and have them dynamically update with daily sales for example. Can anyone help me with this . . . I am trying to find a way to insert excel charts and tables into my publisher 2007 webpage and have them automatically update when the excel files update - I do not want to copy and paste and re-publish everyday ...