Macro: Change chart source data without using Activate

[Naty]

I'm trying to create a macro that changes the source data from specific cells for different cases. The button to run the macro is in Sheet1 and the charts are in Sheet2. My code so far is:

Sheets("Sheet2").ChartObjects("Chart 1").Activate
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B11:B17,T11:T17,AD11:AD17")


My problem is: I don't want Excel to go to Sheet2 when running the macro. In other words, I don't want to use the Activate method in the macro, I want the charts to update without going to Sheet2.

I thought this would work, but it doesn't:

Sheets("Sheet2").ChartObjects("Chart 1").SetSourceData Source:=Sheets("Sheet1").Range("B11:B17,T11:T17,AD11:AD17")

Any ideas?

 

[Derek Brown]

Why do you not want to use 'Activate'?
You can prevent the screen changing by using "Application.ScreenUpdating" and worksheet events from firing using "Application.EnableEvents".

 

[Naty]

That's exactly what I wanted to prevent, the screen changing. I'll use Application.ScreenUpdating

Thank you!!!

 

[Jon Peltier]

You left out a keyword:

Sheets("Sheet2").ChartObjects("Chart 1").Chart.SetSourceData Source:=Sheets("Sheet1").Range("B11:B17,T11:T17,AD11:AD17")

 

[Naty]

Oh, good to know that! I didn't know that I had to repeat Chart again.

Thanks!