graph mods using macros

[JRS]

Hi people,

could really use some urgent help if possible...

I have a macro that reads some data and creates a pivot table and chart onto the existing spreadsheet.

I now want to be able to specify where the chart is positioned (already sorted for the table). And also the dimensions of the chart.
I have tried creating the chart then recording a new macro while I manually move and resize the chart using the mouse, but the only code I get is "incremement xxx" and "Scale xxx" I was wondering if there is a more definitive way I can say the position and the size of the chart without it just remembering my previous mouse movements.


Any help would be greatly appreciated.
Many Thanks
JRS

 

[pgc01]

Hi JRS

An example:

Your chart is called "MyChart", is in worksheet "Sheet1" and you want it to overlap the range J2:Q5.

Try:

Sub PositionChart()
Dim chtO As ChartObject
Dim r As Range
 
With Worksheets("Sheet1")
    Set chtO = .ChartObjects("MyChart")
    Set r = .Range("J2:Q15")
End With
 
With chtO
    .Left = r.Left
    .Top = r.Top
    .Width = r.Width
    .Height = r.Height
End With
End Sub