Macro for Graphing Current Sheet?

[Losse]

I would like to have my macro graph the sheet I am on without having to type in different names as I change different files. The SourceData looks like this right now:

ActiveChart.SetSourceData Source:=Sheets("sName").Range("A1:B400"), PlotBy _
:=xlColumns

Is there any way I can get sName to be the name of the current worksheet I am viewing?

[Losse]

Can anyone help?

[Tim Williams]

ActiveChart.SetSourceData Source:=ActiveSheet.Range("A1:B400"), PlotBy
:=xlColumns

Tim


"Losse" <Losse.1rggwe_1120162141.7334@excelforum-nospam.com> wrote in
message news:Losse.1rggwe_1120162141.7334@excelforum-nospam.com...
>
> I would like to have my macro graph the sheet I am on without having
> to
> type in different names as I change different files. The SourceData
> looks like this right now:
>
> ActiveChart.SetSourceData Source:=Sheets("sName").Range("A1:B400"),
> PlotBy _
> :=xlColumns
>
> Is there any way I can get sName to be the name of the current
> worksheet I am viewing?
>
>
> --
> Losse
> ------------------------------------------------------------------------
> Losse's Profile:
> http://www.excelforum.com/member.php...o&userid=24813
> View this thread:
> http://www.excelforum.com/showthread...hreadid=383681
>

[Losse]

That only gives me an error. It tells me "Object doesn't support this property or method." I belive there needs to be an "_" after the PlotBy as well.

[Losse]

Is what I'm asking impossible?

[Tim Williams]

Did the line you posted actually work? I was assuming it did....

You could always try

dim sName as string

sName = activesheet.name

ActiveChart.SetSourceData _
Source:=Sheets(sName).Range("A1:B400"), _
PlotBy :=xlColumns

Tim

"Losse" <Losse.1rhs2n_1120223253.5446@excelforum-nospam.com> wrote in
message news:Losse.1rhs2n_1120223253.5446@excelforum-nospam.com...
>
> That only gives me an error. It tells me "Object doesn't support
> this
> property or method." I belive there needs to be an "_" after the
> PlotBy
> as well.
>
>
> --
> Losse
> ------------------------------------------------------------------------
> Losse's Profile:
> http://www.excelforum.com/member.php...o&userid=24813
> View this thread:
> http://www.excelforum.com/showthread...hreadid=383681
>

[Jon Peltier]

Obviously the newsreader inserted a line feed between "PlotBy" and ":=".
Does the code work when it's all on a single line? If your first posted
code worked, so should Tim's altered code.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Losse wrote:

> That only gives me an error. It tells me "Object doesn't support this
> property or method." I belive there needs to be an "_" after the PlotBy
> as well.
>
>

[carloshernandezy@gmail.com]

Today i have solved the same problem you have this code is in one
Workbook and creates a chart in the active workbook the values are in
column "C".

Sub Grafico()
Dim wb As Workbook
Dim ws As Worksheet
Dim cht As Excel.Chart


Set wb = ActiveWorkbook
'

Set ws = wb.Sheets(1)

'nombra la hoja actual
ws.Name = "TOC"

'Define el rango de trabajo RngC
wb.Names.Add Name:="RngC", RefersToR1C1:= _
"=OFFSET(TOC!R1C3,0,0,COUNTA(TOC!C3))"

Application.ScreenUpdating = False
Set cht = ws.ChartObjects.Add(50, 50, 400, 300).Chart



With cht
.ChartType = xlXYScatterSmoothNoMarkers



..SetSourceData Source:=ws.Range("TOC!RngC"), PlotBy:=xlColumns
.Location Where:=xlLocationAsObject, Name:="TOC"
.HasTitle = True
.ChartTitle.Characters.Text = "TOC en "
With .Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With .Axes(xlValue)
.HasTitle = True
.AxisTitle.Characters.Text = "ppb"
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
.HasLegend = False
With .PlotArea
With .Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
With .Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
End With
End With
Application.ScreenUpdating = True


'Quita las unidades del eje X
ActiveChart.Axes(xlCategory).Select
ActiveChart.ChartArea.Select
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = False
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
End Sub

Jon Peltier wrote:
> Obviously the newsreader inserted a line feed between "PlotBy" and ":=".
> Does the code work when it's all on a single line? If your first posted
> code worked, so should Tim's altered code.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
>
> Losse wrote:
>
> > That only gives me an error. It tells me "Object doesn't support this
> > property or method." I belive there needs to be an "_" after the PlotBy
> > as well.
> >
> >

[Losse]

Thank you, Tim. That worked perfectly for me. It did not work, however, until I changed it into the format you posted.