Tom
Your macro probably contains the line: Charts.Add
When this is executed the chart becomes the activesheet which is why you
can't reference Activesheet to retrieve the data.
What you need to do is create a reference to the activesheet before starting
to create the chart. Something like this:
Dim dataSheet As Worksheet
Set dataSheet = ActiveSheet
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=dataSheet.Range("A4:A13,C4:C13")
etc
Hope this helps
Rowan
"Tom9283" wrote:
Hi,
I want to create a macro that I can run on various sheets. All these sheets
have the same format but different data. I created the macro and opened it
with microsoft visual basic. The problem is the '=Sheets()' command that
selects the sheet that i used to record the data. How do I change it so it
just runs on the active sheet?
ActiveChart.SetSourceData Source:=Sheets("S&P").Range("A4:A13,C4:C13"),
Thanks
|