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

T

Tom9283

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
 
R

Rowan

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
 
T

Tom9283

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,D4:D13"), _
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
 
R

Rowan

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

Regards
Rowan
 
T

Tom9283

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.
 
R

Rowan

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,D4:D13"), _
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
 
T

Tom9283

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

Rowan said:
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,D4:D13"), _
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
Click to expand...
 
Top