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

Answer: VBA Copy Chart as Picture
 

Yes, it is possible to copy the chart as a picture to the range without selecting the range each time. You can use the method of the chart object to save the chart as a picture file and then use the method of the Shapes collection to insert the picture into the worksheet.

Here's an updated version of the code that uses this approach:

1. Declare the necessary variables:
   
   Code:

   ---

   Dim Rng As Range
Dim Cht As Chart
Dim PicFileName As String
Dim PicShape As Shape

   ---

2. Set the chart object:
   
   Code:

   ---

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

   ---

3. Loop through the range:
   
   Code:

   ---

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

   ---

4. Save the chart as a picture file:
   
   Code:

   ---

   PicFileName = Environ$("temp") & "\" & Format(Now, "yyyymmddhhmmss") & ".png"
Cht.Export Filename:=PicFileName, FilterName:="PNG"

   ---

5. Insert the picture into the worksheet:
   
   Code:

   ---

   Set PicShape = ActiveSheet.Shapes.AddPicture(PicFileName, False, True, Rng.Left, Rng.Top, Rng.Width, Rng.Height)

   ---

6. Delete the picture file:
   
   Code:

   ---

   Kill PicFileName

   ---

7. Close the loop:
   
   Code:

   ---

   Next Rng

   ---

This code saves the chart as a PNG file in the user's temporary folder, inserts the picture into the worksheet using the method of the Shapes collection, and then deletes the picture file. The False and True arguments in the method specify that the picture should not be linked to the file and should be inserted with its original size, respectively. The Left, Top, Width, and Height properties of the range Rng are used to position and size the picture in the worksheet.

VBA Copy Chart as Picture
 

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" wrote in message
...
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

VBA Copy Chart as Picture
 

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" wrote in message
...
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