VBA Copy Chart as Picture

[Steph]

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.

 

[Andy Pope]

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

 

[Steph]

Andy,

This is perfect. Thank you so much!