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

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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
>