VBA Copy Chart as Picture

S

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.
 
A

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top