How to change macro so it performs actions on ACTIVE sheet?

[Guest]

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

 

[Guest]

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

 

[Guest]

Hi Rowan,

I appreciate the reply. When I tried to run it, it still gave me the same
thing. Probably because of the following line:
ActiveChart.Location Where:=xlLocationAsObject, Name:="S&P"

I tried setting Name:=dataSheet but that gave me an error..

Thanks again, appreciate the help.

Below is a copy of the code:

Dim dataSheet As Worksheet
Set dataSheet = ActiveSheet
Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SetSourceData Source:=dataSheet.Range("B4:B13,D413"), _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="S&P"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Week"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Weekly Return"
End With
ActiveChart.HasLegend = False
ActiveSheet.Shapes("Chart 8").IncrementLeft -228.75
ActiveSheet.Shapes("Chart 8").IncrementTop 18#
ActiveWindow.Visible = False
Windows("FI310 Term Project Weekly Values.xls").Activate
ActiveCell.Offset(-1, 3).Range("A1").Select
End Sub

 

[Guest]

You nearly had it. Try
ActiveChart.Location Where:=xlLocationAsObject, Name:=dataSheet.Name

Regards
Rowan

 

[Guest]

Awesome. Thank you so much.

Now there is only one problem left. The problem is that it doesn't
reposition the chart. It's because of this "chart 8" in the code below. How
can I change that so it selects the chart that was just created. If it's too
complicated just let me know, as it's really just a minor thing.

Thanks again.

 

[Guest]

Tom

I'm not sure about the best way to identify the chart you just created. If
you run the code like this though it will reposition any chart on the sheet.
So if you only have one chart per worksheet then you should be OK

Dim dataSheet As Worksheet
Dim i As Integer
Set dataSheet = ActiveSheet

Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.Name = "myChart"
ActiveChart.SetSourceData Source:=dataSheet.Range("B4:B13,D413"), _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:=dataSheet.Name
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Week"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Weekly Return"
End With
ActiveChart.HasLegend = False
For i = 1 To dataSheet.Shapes.Count
If Left(dataSheet.Shapes(i).Name, 5) = "Chart" Then
dataSheet.Shapes(i).IncrementLeft -228.75
dataSheet.Shapes(i).IncrementTop 18#
End If
Next i
Etc

Rowan

 

[Guest]

yes, I only Have 1 chart. Thanks a bunch!

Tom

I'm not sure about the best way to identify the chart you just created. If
you run the code like this though it will reposition any chart on the sheet.
So if you only have one chart per worksheet then you should be OK

Dim dataSheet As Worksheet
Dim i As Integer
Set dataSheet = ActiveSheet

Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.Name = "myChart"
ActiveChart.SetSourceData Source:=dataSheet.Range("B4:B13,D413"), _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:=dataSheet.Name
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Week"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Weekly Return"
End With
ActiveChart.HasLegend = False
For i = 1 To dataSheet.Shapes.Count
If Left(dataSheet.Shapes(i).Name, 5) = "Chart" Then
dataSheet.Shapes(i).IncrementLeft -228.75
dataSheet.Shapes(i).IncrementTop 18#
End If
Next i
Etc

Rowan