how to move a pivot chart with vba

Question

i am trying to make a function that creates a chart next to the pivot table but cant make the chart move =( display the right data and works fine, its just been created to far away from the actual pivot table.

Function chart_from_pivot(a_pivot As PivotTable) As Chart
Debug.Print 0

Dim objChart As Chart
Set objChart = Charts.Add

Debug.Print 1

objChart.SetSourceData a_pivot.TableRange1
Debug.Print 2
objChart.ChartType = xl3DColumn
Debug.Print 3
objChart.Location Where:=xlLocationAsObject, Name:=a_pivot.Parent.Name
Debug.Print 4

'HERE IS THE ISSUE!!!!
'objChart.Parent.Left = Range("D2").Left
'Debug.Print 5
'objChart.Parent.Top = Range("D2").Top
'Debug.Print 6

'objChart.ChartStyle = 294
'Debug.Print 7

chart_from_pivot = objChart

End Function

any recommendations? thanks guys.

If left to itself, the chart goes to the center of the screen near where the cursor was last in your spreadsheet. Try setting a cursor location first in your code. — Sam, Apr 04 '16 at 19:46

score 0 · Accepted Answer · answered Apr 06 '16 at 18:41

I needed to set the objchart with the objchart.Location once i added the set now it works =)

this is the final code in case some one needed in the future.

Function chart_from_pivot(a_pivot As PivotTable) As Chart
    Dim objChart As Chart
    Set objChart = Charts.Add

    objChart.SetSourceData a_pivot.TableRange1
    objChart.ChartType = xl3DColumn
    Set objChart = objChart.Location(Where:=xlLocationAsObject, Name:=a_pivot.Parent.Name)

    objChart.ChartStyle = 294

    objChart.ChartArea.Left = ThisWorkbook.Sheets("Charts").Range("B10").Left
    objChart.ChartArea.Top = ThisWorkbook.Sheets("Charts").Range("B10").Top   
    chart_from_pivot = objChart
End Function

how to move a pivot chart with vba

1 Answers1