View Single Post
  #2   Report Post  
Rowan
 
Posts: n/a
Default

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