VBA Y Axis Adjustment - VBA only works on 1 sheet?

[trrbrg]

Hi!

I've pieced together some VBA from the internet to adjust the Y axis based on the calculated results of specific cells. The VBA works wonderfully for the chart on the first tab.

The problem is, when I copy and paste the VBA to the next sheet, adjust the cell references and the chart name, it doesn't work for the second chart. It adjusts the axis, but incorrectly.

This is the VBA that I am using:

Option Explicit
Private Sub Worksheet_Calculate()
Dim cht As Chart
Dim wks As Worksheet


Set wks = ActiveSheet
Set cht = wks.ChartObjects("Chart District").Chart


If wks.Range("$H$2").Value <> cht.Axes(xlValue).MaximumScale Then
cht.Axes(xlValue).MaximumScale = wks.Range("$H$2").Value
End If
If wks.Range("$I$2").Value <> cht.Axes(xlValue).MinimumScale Then
cht.Axes(xlValue).MinimumScale = wks.Range("$I$2").Value
End If
If wks.Range("$J$2").Value <> cht.Axes(xlValue).MajorUnit Then
cht.Axes(xlValue).MajorUnit = wks.Range("$J$2").Value
End If
End Sub

Does anyone have any ideas as to why this won't work on multiple tabs?

 

[Andrew Poulsom]

Try replacing:

Set wks = ActiveSheet

with:

Set wks = Me

 

[trrbrg]

Try replacing:

Set wks = ActiveSheet

with:

Set wks = Me

Thanks, that worked great!