Hello everyone,
I have a chart that its x axis is controlled by a simple set of code that I wrote in the sheet as below:
Private Sub Worksheet_Calculate()
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.Axes(xlValue).MinimumScale = Sheets("Chart").Range("S10").Value
ActiveChart.Axes(xlValue).MaximumScale = Sheets("Chart").Range("S11").Value
ActiveChart.Axes(xlValue).MajorUnit = Sheets("Chart").Range("S12").Value
ActiveCell.Select
End Sub
It works just fine in the way that I want the X axis to change automatically with the data range. However, when I tried to refresh the other pivot tables in other separate tags (basing on the same set of data as the chart) or add more records to the source data, the error msg pop up and l know it has something to do with this line: ActiveSheet.ChartObjects("Chart 4").Activate
So I tried to replace ActiveSheet. with Sheets("Chart") and the error disappear but the problem is that I would go back to the Sheet "Chart" whenever I refresh the other pivot tables in other separate sheets, so I know there must be better solution than this one, I want to stay at the same page when I refresh the other tables.
Can anyone help? Attached is the file.
Thanks in advance.
1.PNG
Bookmarks