Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 15th 05, 05:13 AM
Tom9283
 
Posts: n/a
Default 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   Report Post  
Old April 15th 05, 05:31 AM
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

  #3   Report Post  
Old April 15th 05, 05:45 AM
Tom9283
 
Posts: n/a
Default

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,D413"), _
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   Report Post  
Old April 15th 05, 06:11 AM
Rowan
 
Posts: n/a
Default

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,D413"), _
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   Report Post  
Old April 15th 05, 06:23 AM
Tom9283
 
Posts: n/a
Default

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   Report Post  
Old April 15th 05, 06:56 AM
Rowan
 
Posts: n/a
Default

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 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   Report Post  
Old April 15th 05, 07:32 AM
Tom9283
 
Posts: n/a
Default

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,D413"), _
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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
macro color change Dan Excel Worksheet Functions 0 February 15th 05 09:35 PM
macro to change the names and delete closed books Tim Excel Discussion (Misc queries) 2 February 6th 05 09:39 PM
how do i change the expense statement template macro Mad2691 Excel Worksheet Functions 1 January 28th 05 01:21 PM
Where to stick macro to change default comment font? [email protected] Excel Discussion (Misc queries) 1 January 1st 05 12:57 AM
Macro button colour change??? Beefyme Excel Worksheet Functions 1 November 19th 04 06:15 PM


All times are GMT +1. The time now is 03:32 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017