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:
- Declare the necessary variables:
Code:
Dim Rng As Range
Dim Cht As Chart
Dim PicFileName As String
Dim PicShape As Shape
- Set the chart object:
Code:
Set Cht = Sheets("MC").ChartObjects("Cht1").Chart
- Loop through the range:
Code:
For Each Rng In Sheets("Dash").Range("F4:F11")
- Save the chart as a picture file:
Code:
PicFileName = Environ$("temp") & "\" & Format(Now, "yyyymmddhhmmss") & ".png"
Cht.Export Filename:=PicFileName, FilterName:="PNG"
- Insert the picture into the worksheet:
Code:
Set PicShape = ActiveSheet.Shapes.AddPicture(PicFileName, False, True, Rng.Left, Rng.Top, Rng.Width, Rng.Height)
- Delete the picture file:
- Close the loop:
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.