Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to change macro so it performs actions on ACTIVE sheet?
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 Whe=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 Whe=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 |
#4
|
|||
|
|||
You nearly had it. Try
ActiveChart.Location Whe=xlLocationAsObject, Name:=dataSheet.Name Regards Rowan "Tom9283" wrote: 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 Whe=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 Whe=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 |
#5
|
|||
|
|||
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. ActiveSheet.Shapes("Chart 8").IncrementLeft -228.75 ActiveSheet.Shapes("Chart 8").IncrementTop 18# "Rowan" wrote: You nearly had it. Try ActiveChart.Location Whe=xlLocationAsObject, Name:=dataSheet.Name Regards Rowan |
#6
|
|||
|
|||
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 Whe=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 "Tom9283" wrote: 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. ActiveSheet.Shapes("Chart 8").IncrementLeft -228.75 ActiveSheet.Shapes("Chart 8").IncrementTop 18# "Rowan" wrote: You nearly had it. Try ActiveChart.Location Whe=xlLocationAsObject, Name:=dataSheet.Name Regards Rowan |
#7
|
|||
|
|||
yes, I only Have 1 chart. Thanks a bunch!
"Rowan" wrote: 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 Whe=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 "Tom9283" wrote: 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. ActiveSheet.Shapes("Chart 8").IncrementLeft -228.75 ActiveSheet.Shapes("Chart 8").IncrementTop 18# "Rowan" wrote: You nearly had it. Try ActiveChart.Location Whe=xlLocationAsObject, Name:=dataSheet.Name Regards Rowan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro color change | Excel Worksheet Functions | |||
macro to change the names and delete closed books | Excel Discussion (Misc queries) | |||
how do i change the expense statement template macro | Excel Worksheet Functions | |||
Where to stick macro to change default comment font? | Excel Discussion (Misc queries) | |||
Macro button colour change??? | Excel Worksheet Functions |