Automatically update minimum chart axis value from Pivot Table

[E124]

Hi,


I have a pivot table where I used "summarize the value", by min to determine the lowest value in a range. I want to automatically make that value my minimum chart axis value every time I change the filters for the pivot table.


When I do a cell reference in a random cell for the min value in the pivot table, it reads =GETPIVOTDATA("Min of Date Assigned",$A$7).


My chart is located on a different sheet than the pivot table. The chart sheet is named "Dates". The chart name is "Chart 1". The pivot table is located in a sheet named "PV1". The pivot tables names is "PV1".


I have tried the recorder but it returns an error when I run it. Below is the code.


Sub Macro6()

'

' Macro6 Macro

'

' Keyboard Shortcut: Ctrl+d

'

Sheets("PV1").Select

ActiveWindow.SmallScroll Down:=21

Range("B39").Select

Selection.Copy

Sheets("Dates").Select

ActiveSheet.ChartObjects("Chart 1").Activate

ActiveChart.Axes(xlValue).Select

ActiveChart.Axes(xlValue).MinimumScale = 40711

End Sub


40711 should change everytime because of the filters. It should be the min value. I have little exp with VBA. Any help would be most appreciated. Thank you for your time.

 

[Hui]

E124


Firstly, Welcome to the Chandoo.org Forums

Try changing to this:

[pre]

Sub Macro6()
' Macro6 Macro
' Keyboard Shortcut: Ctrl+d
Sheets("Dates").Select
ActiveSheet.ChartObjects("Chart 1").Axes(xlValue).MinimumScale = workSheets("PV1").Range("B39").Value

End Sub

[/pre]

 

[E124]

Thank you for your welcome and help, Hui!


I am getting the following error. Run time error 438 Object doesn't support this project or method.


It highlights the following:

ActiveSheet.ChartObjects("Chart 1").Axes(xlValue).MinimumScale = workSheets("PV1").Range("B39").Value

 

[Hui]

Whoops, forgot about a Chart...

I shouldn't answer questions from my phone, where I can't test answers.

[pre]

Sub Macro6()
Sheets("Dates").Select
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue).MinimumScale = Worksheets("PV1").Range("B39").Value
End Sub

[/pre]

 

[E124]

I got a run time error 9. Subscript out of range.


B39 is referencing my pivot table which grows and shrinks according to the filters.


Would this be the cause? I tried replacing it with a few different things with no results.


Thank you, Hui.

 

[E124]

Hui,


I referenced the Pivot Table cell in another cell and referenced the new cell in the code.


Thank you so much!