Macro for Graphing Current Sheet?

[Losse]

I would like to have my macro graph the sheet I am on without having t
type in different names as I change different files. The SourceDat
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 curren
worksheet I am viewing

 

[Losse]

Can anyone help

 

[Tim Williams]

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

Tim

 

[Losse]

That only gives me an error. It tells me "Object doesn't support thi
property or method." I belive there needs to be an "_" after the PlotB
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

 

[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/
_______

 

[carloshernandezy]

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

 

[Losse]

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