setting source data

having a bit of trouble setting up a chart in my macro - the worksheet has been assigned to the variable mynewSheet and the data for the chart is in a range called mynewTable (inside mynewSheet). I need whats in column A (a list of team names)to go across the horizontal and column O(number of goals scored) to be the vertical.
tried something like the following which didnt work

Charts.Add
ActiveChart.ChartType = xl3DColumnStacked
ActiveChart.SetSourceData _
    Source:=mynewSheet(mynewTable.Range("A:0"))
ActiveChart.Location xlLocationAsObject, Name:="chartdata"

Set it up manually with the macro recorder switched on - you'll get the
basic code which you can then adapt.

--
Cheers
Nigel



"cjsmith22" <cjsmith22.1yi05s_1131990959.284@excelforum-nospam.com> wrote in
message news:cjsmith22.1yi05s_1131990959.284@excelforum-nospam.com...
>
> having a bit of trouble setting up a chart in my macro - the worksheet
> has been assigned to the variable mynewSheet and the data for the chart
> is in a range called mynewTable (inside mynewSheet). I need whats in
> column A (a list of team names)to go across the horizontal and column
> O(number of goals scored) to be the vertical.
> tried something like the following which didnt work
>
>
> Code:
> --------------------
> Charts.Add
> ActiveChart.ChartType = xl3DColumnStacked
> ActiveChart.SetSourceData _
> Source:=mynewSheet(mynewTable.Range("A:0"))
> ActiveChart.Location xlLocationAsObject, Name:="chartdata"
> --------------------
>
>
> --
> cjsmith22
> ------------------------------------------------------------------------
> cjsmith22's Profile:
http://www.excelforum.com/member.php...o&userid=28528
> View this thread: http://www.excelforum.com/showthread...hreadid=484925
>

cheers nigel - couple of problems though - i recorded a macro to set up a chart and i got the following code:

Charts.Add
    ActiveChart.ChartType = xlColumnStacked
    ActiveChart.SetSourceData Source:=Sheets("Update 8").Range("O19:O38,A19:A38") _
        , PlotBy:=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Update 8"
    With ActiveChart
        .HasTitle = False
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    End With

As the name of the sheet will change every month (update 8 to update 9,10,11 etc) but it will always be the last sheet in the book, i set 'update 8' to the variable 'mynewSheet' ( Set mynewSheet = myWorkbook.Worksheets(sCount) ) also , the range from where the data is taken ("O19:O38,A19:A38") will also change every month so i set up the variable 'myF'(a range in mynewSheet) (the data will always be in columns O and A of 'myF') - how do i incorporate these two into SetSourceData ?

The simplest answer would be .......

ActiveChart.SetSourceData Source:=myNewSheet.myF _
, PlotBy:=xlColumns

but I suspect your range myF is not just the range for the chart data? If
it is then all well and good.

--
Cheers
Nigel



"cjsmith22" <cjsmith22.1yi43d_1131996031.9398@excelforum-nospam.com> wrote
in message news:cjsmith22.1yi43d_1131996031.9398@excelforum-nospam.com...
>
> cheers nigel - couple of problems though - i recorded a macro to set up
> a chart and i got the following code:
>
> Code:
> --------------------
> Charts.Add
> ActiveChart.ChartType = xlColumnStacked
> ActiveChart.SetSourceData Source:=Sheets("Update
8").Range("O19:O38,A19:A38") _
> , PlotBy:=xlColumns
> ActiveChart.Location Where:=xlLocationAsObject, Name:="Update 8"
> With ActiveChart
> .HasTitle = False
> .Axes(xlCategory, xlPrimary).HasTitle = False
> .Axes(xlValue, xlPrimary).HasTitle = False
> End With
> --------------------
>
> As the name of the sheet will change every month (update 8 to update
> 9,10,11 etc) but it will always be the last sheet in the book, i set
> 'update 8' to the variable 'mynewSheet' ( Set mynewSheet =
> myWorkbook.Worksheets(sCount) ) *also* , the range from where the data
> is taken ("O19:O38,A19:A38") will also change every month so i set up
> the variable 'myF'(a range in mynewSheet) (the data will always be in
> columns O and A of 'myF') - how do i incorporate these two into
> SetSourceData ?
>
>
> --
> cjsmith22
> ------------------------------------------------------------------------
> cjsmith22's Profile:
http://www.excelforum.com/member.php...o&userid=28528
> View this thread: http://www.excelforum.com/showthread...hreadid=484925
>