setting source data

[cjsmith22]

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"

 

[sweater_vests_rock]

cj.

this worked for me:

Sub CreateChart()
    
    Dim mynewSheet As Worksheet
    Dim mynewTable As Range
    
    Set mynewSheet = ActiveSheet
    Set mynewTable = Union(mynewSheet.Range("A14:A18"), mynewSheet.Range("O14:O18"))
    
    Charts.Add
    ActiveChart.ChartType = xl3DColumnStacked
    ActiveChart.SetSourceData Source:=mynewTable, PlotBy:= _
        xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:=mynewSheet.Name
    ActiveChart.Legend.Delete
End Sub

since you have declared your mynewTable range, no need to reference the worksheet. also, not sure why you are using a relative range reference...aren't you being redundant?

hope this helps. ben.

 

[cjsmith22]

cheers ben - why is it this line works

Set mynewTablec = Union(mynewSheet.Range("A1:A20"), f.Range("O1:O20"))

but this doesn't ?

Set mynewTablec = Union(mynewSheet.Range(A:A), f.Range(O:O))

(so that instead of using rows 1-20 of A and O it uses however many cells are in column A and O of mynewSheet)