Setting Source Range For Chart
 

I have this macro basically done, I am having a problem with settign the source range for the chart though. I think it is simply a syntax error but I am not sure - any advice would be helpful as I am stuck. Here is the part of the code

Dim LastDataRow As Lon
LastDataRow = Cells(Rows.Count, "C").End(xlUp).Ro
If LastDataRow = 1 Then GoTo MyErrorHandle
Els
Sheets("InputSheet").Visible = Fals
Sheets("Chart1").Visible = Tru
Sheets("Chart1").Selec

With ActiveChar
.SetSourceData Source:=Workbooks("Main.xls").Sheets("DataSheet"). Range("C1", Range("H" & LastDataRow)),
PlotBy:=xlColumn
.HasTitle = Tru
.ChartTitle.Characters.Text = "Chart
.Axes(xlValue, xlPrimary).HasTitle = Tru
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Percentage
.HasAxis(xlCategory, xlPrimary) = Tru
.HasAxis(xlValue, xlPrimary) = Tru
.Axes(xlCategory, xlPrimary).CategoryType = xlAutomati
.HasLegend = Fals
.HasDataTable = Tru
.DataTable.ShowLegendKey = Tru
End Wit
End I
Exit Su

MyErrorHandler
'"Whole bunch of stuff here
End Su

I am getting the error "Mehtod 'Range' of Object '_Global' Failed" on the .SetSourceData command. And I get some problems with my "if" and "else" statements. What am I doing wrong - looking at it, it should work, I just don't know where I am going wrong

Thanks for any advice

Jim

Setting Source Range For Chart
 

Hi James,

The problem is that the use of Range("H" & LastDataRow) will fail
because the activesheet is a chart sheet not a worksheet.

So try this small modification.

..SetSourceData Source:= _
ThisWorkbook.Sheets("Sheet2").Range("C1", "H" & CStr(LastDataRow)), _
PlotBy:=xlColumns

Cheers
Andy

James Stephens wrote:

I have this macro basically done, I am having a problem with settign the source range for the chart though. I think it is simply a syntax error but I am not sure - any advice would be helpful as I am stuck. Here is the part of the code:

Dim LastDataRow As Long
LastDataRow = Cells(Rows.Count, "C").End(xlUp).Row
If LastDataRow = 1 Then GoTo MyErrorHandler
Else
Sheets("InputSheet").Visible = False
Sheets("Chart1").Visible = True
Sheets("Chart1").Select

With ActiveChart
.SetSourceData Source:=Workbooks("Main.xls").Sheets("DataSheet"). Range("C1", Range("H" & LastDataRow)), _
PlotBy:=xlColumns
.HasTitle = True
.ChartTitle.Characters.Text = "Chart"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Percentage"
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
.HasLegend = False
.HasDataTable = True
.DataTable.ShowLegendKey = True
End With
End If
Exit Sub

MyErrorHandler:
'"Whole bunch of stuff here"
End Sub

I am getting the error "Mehtod 'Range' of Object '_Global' Failed" on the .SetSourceData command. And I get some problems with my "if" and "else" statements. What am I doing wrong - looking at it, it should work, I just don't know where I am going wrong.

Thanks for any advice,

Jim

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Setting Source Range For Chart
 

Try it like this:

Dim LastDataRow As Long
LastDataRow = Cells(Rows.Count, "C").End(xlUp).Row
If LastDataRow = 1 Then
GoTo MyErrorHandler
Else
Sheets("InputSheet").Visible = False
Sheets("Chart1").Visible = True
Sheets("Chart1").Select

With ActiveChart
Set Sh = Workbooks("Main.xls").Sheets("DataSheet")
.SetSourceData Source:=Workbooks("Main.xls"). _
Sh.Range(Sh.Range("C1"), Sh.Range("H" & LastDataRow)), _
PlotBy:=xlColumns
.HasTitle = True
.ChartTitle.Characters.Text = "Chart"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Percentage"
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
.HasLegend = False
.HasDataTable = True
.DataTable.ShowLegendKey = True
End With
End If
Exit Sub

MyErrorHandler:
'"Whole bunch of stuff here"
End Sub

--
Regards,
Tom Ogilvy

"James Stephens" wrote in message
...
I have this macro basically done, I am having a problem with settign the
source range for the chart though. I think it is simply a syntax error but
I am not sure - any advice would be helpful as I am stuck. Here is the part
of the code:

Dim LastDataRow As Long
LastDataRow = Cells(Rows.Count, "C").End(xlUp).Row
If LastDataRow = 1 Then GoTo MyErrorHandler
Else
Sheets("InputSheet").Visible = False
Sheets("Chart1").Visible = True
Sheets("Chart1").Select

With ActiveChart
.SetSourceData
Source:=Workbooks("Main.xls").Sheets("DataSheet"). Range("C1", Range("H" &
LastDataRow)), _
PlotBy:=xlColumns
.HasTitle = True
.ChartTitle.Characters.Text = "Chart"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Percentage"
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
.HasLegend = False
.HasDataTable = True
.DataTable.ShowLegendKey = True
End With
End If
Exit Sub

MyErrorHandler:
'"Whole bunch of stuff here"
End Sub

I am getting the error "Mehtod 'Range' of Object '_Global' Failed" on the
..SetSourceData command. And I get some problems with my "if" and "else"
statements. What am I doing wrong - looking at it, it should work, I just
don't know where I am going wrong.

Thanks for any advice,

Jim

Setting Source Range For Chart
 

Whoops left in some residual code that should have been removed. :

Dim LastDataRow As Long
LastDataRow = Cells(Rows.Count, "C").End(xlUp).Row
If LastDataRow = 1 Then
GoTo MyErrorHandler
Else
Sheets("InputSheet").Visible = False
Sheets("Chart1").Visible = True
Sheets("Chart1").Select

Set Sh = Workbooks("Main.xls").Sheets("DataSheet")
With ActiveChart
.SetSourceData Source:= Sh.Range(Sh.Range("C1"), _
Sh.Range("H" & LastDataRow)), _
PlotBy:=xlColumns
.HasTitle = True
.ChartTitle.Characters.Text = "Chart"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Percentage"
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
.HasLegend = False
.HasDataTable = True
.DataTable.ShowLegendKey = True
End With
End If
Exit Sub

MyErrorHandler:
'"Whole bunch of stuff here"
End Sub

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
Try it like this:

Dim LastDataRow As Long
LastDataRow = Cells(Rows.Count, "C").End(xlUp).Row
If LastDataRow = 1 Then
GoTo MyErrorHandler
Else
Sheets("InputSheet").Visible = False
Sheets("Chart1").Visible = True
Sheets("Chart1").Select

With ActiveChart
Set Sh = Workbooks("Main.xls").Sheets("DataSheet")
.SetSourceData Source:=Workbooks("Main.xls"). _
Sh.Range(Sh.Range("C1"), Sh.Range("H" & LastDataRow)), _
PlotBy:=xlColumns
.HasTitle = True
.ChartTitle.Characters.Text = "Chart"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Percentage"
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
.HasLegend = False
.HasDataTable = True
.DataTable.ShowLegendKey = True
End With
End If
Exit Sub

MyErrorHandler:
'"Whole bunch of stuff here"
End Sub

--
Regards,
Tom Ogilvy

"James Stephens" wrote in message
...
I have this macro basically done, I am having a problem with settign
the
source range for the chart though. I think it is simply a syntax error
but
I am not sure - any advice would be helpful as I am stuck. Here is the
part
of the code:

Dim LastDataRow As Long
LastDataRow = Cells(Rows.Count, "C").End(xlUp).Row
If LastDataRow = 1 Then GoTo MyErrorHandler
Else
Sheets("InputSheet").Visible = False
Sheets("Chart1").Visible = True
Sheets("Chart1").Select

With ActiveChart
.SetSourceData
Source:=Workbooks("Main.xls").Sheets("DataSheet"). Range("C1", Range("H" &
LastDataRow)), _
PlotBy:=xlColumns
.HasTitle = True
.ChartTitle.Characters.Text = "Chart"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
"Percentage"
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
.HasLegend = False
.HasDataTable = True
.DataTable.ShowLegendKey = True
End With
End If
Exit Sub

MyErrorHandler:
'"Whole bunch of stuff here"
End Sub

I am getting the error "Mehtod 'Range' of Object '_Global' Failed" on
the
.SetSourceData command. And I get some problems with my "if" and "else"
statements. What am I doing wrong - looking at it, it should work, I just
don't know where I am going wrong.

Thanks for any advice,

Jim

Setting Source Range For Chart
 

Thanks, seems to work great, and now I understand where my problem was

Greatly appreciate the help

Ji

----- Tom Ogilvy wrote: ----


Whoops left in some residual code that should have been removed.

Dim LastDataRow As Lon
LastDataRow = Cells(Rows.Count, "C").End(xlUp).Ro
If LastDataRow = 1 The
GoTo MyErrorHandle
Els
Sheets("InputSheet").Visible = Fals
Sheets("Chart1").Visible = Tru
Sheets("Chart1").Selec

Set Sh = Workbooks("Main.xls").Sheets("DataSheet"
With ActiveChar
.SetSourceData Source:= Sh.Range(Sh.Range("C1"),
Sh.Range("H" & LastDataRow)),
PlotBy:=xlColumn
.HasTitle = Tru
.ChartTitle.Characters.Text = "Chart
.Axes(xlValue, xlPrimary).HasTitle = Tru
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Percentage
.HasAxis(xlCategory, xlPrimary) = Tru
.HasAxis(xlValue, xlPrimary) = Tru
.Axes(xlCategory, xlPrimary).CategoryType = xlAutomati
.HasLegend = Fals
.HasDataTable = Tru
.DataTable.ShowLegendKey = Tru
End Wit
End I
Exit Su

MyErrorHandler
'"Whole bunch of stuff here
End Su

--
Regards
Tom Ogilv


"Tom Ogilvy" wrote in messag
..
Try it like this
Dim LastDataRow As Lon
LastDataRow = Cells(Rows.Count, "C").End(xlUp).Ro
If LastDataRow = 1 The
GoTo MyErrorHandle
Els
Sheets("InputSheet").Visible = Fals
Sheets("Chart1").Visible = Tru
Sheets("Chart1").Selec
With ActiveChar
Set Sh = Workbooks("Main.xls").Sheets("DataSheet"
.SetSourceData Source:=Workbooks("Main.xls").
Sh.Range(Sh.Range("C1"), Sh.Range("H" & LastDataRow)),
PlotBy:=xlColumn
.HasTitle = Tru
.ChartTitle.Characters.Text = "Chart
.Axes(xlValue, xlPrimary).HasTitle = Tru
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Percentage
.HasAxis(xlCategory, xlPrimary) = Tru
.HasAxis(xlValue, xlPrimary) = Tru
.Axes(xlCategory, xlPrimary).CategoryType = xlAutomati
.HasLegend = Fals
.HasDataTable = Tru
.DataTable.ShowLegendKey = Tru
End Wit
End I
Exit Su
MyErrorHandler
'"Whole bunch of stuff here
End Su
--
Regards
Tom Ogilv
"James Stephens" wrote in messag
..
I have this macro basically done, I am having a problem with settig
th
source range for the chart though. I think it is simply a syntax erro
bu
I am not sure - any advice would be helpful as I am stuck. Here is th
par
of the code
Dim LastDataRow As Lon
LastDataRow = Cells(Rows.Count, "C").End(xlUp).Ro
If LastDataRow = 1 Then GoTo MyErrorHandle
Els
Sheets("InputSheet").Visible = Fals
Sheets("Chart1").Visible = Tru
Sheets("Chart1").Selec
With ActiveChar
.SetSourceDat
Source:=Workbooks("Main.xls").Sheets("DataSheet"). Range("C1", Range("H" & LastDataRow)),
PlotBy:=xlColumn
.HasTitle = Tru
.ChartTitle.Characters.Text = "Chart
.Axes(xlValue, xlPrimary).HasTitle = Tru
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text
"Percentage
.HasAxis(xlCategory, xlPrimary) = Tru
.HasAxis(xlValue, xlPrimary) = Tru
.Axes(xlCategory, xlPrimary).CategoryType = xlAutomati
.HasLegend = False
.HasDataTable = True
.DataTable.ShowLegendKey = True
End With
End If
Exit Sub
MyErrorHandler:
'"Whole bunch of stuff here"
End Sub
I am getting the error "Mehtod 'Range' of Object '_Global' Failed" on
the
.SetSourceData command. And I get some problems with my "if" and "else"
statements. What am I doing wrong - looking at it, it should work, I just
don't know where I am going wrong.
Thanks for any advice,
Jim