Dynamic Ranges for Charts

[Maikel]

Maybe this will give you some ideas.

Sub Macro1()
Dim outstanding As Range
Dim backlog As Range
Set outstanding = Sheet1.Range(Cells(2, 1), Cells(11, 1))
Set backlog = Sheet1.Range(Cells(2, 2), Cells(11, 2))
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Values = outstanding
ActiveChart.SeriesCollection(2).Values = backlog
End Sub
 
Sub Macro2()
Dim outstanding As Range
Dim backlog As Range
Range("A1").Select 'Make sure that no object is selected.
Set outstanding = ActiveSheet.Range("A2", Range("A" & Rows.Count).End(xlUp))
Set backlog = ActiveSheet.Range("B2", Range("B" & Rows.Count).End(xlUp))
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Values = "=Sheet1!" & outstanding.Address(True, True, xlR1C1)
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).Values = "=Sheet1!" & backlog.Address(True, True, xlR1C1)
Range("A1").Select
End Sub

Thank you for your reply i was able to solve what i was looking for.

I still have one last question though. I think it might be obvious for you but considering my experience yet it's not for me.

Why do the following 2 examples differ. (The second version doesn't set a range)

#1

Set seriesRange = Sheet2.Range(Cells(16, col), Cells(18, col))
Sheets("Output").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlPie
With ActiveChart.SeriesCollection.NewSeries
   .Name = Sheet2.Range("A16")
   .Values = seriesRange
   .XValues = Sheet2.Range("A16:A18")
   .ApplyDataLabels
    End With

#2
    Sheets("Output").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlPie
Set seriesRange = Sheet2.Range(Cells(16, col), Cells(18, col))
With ActiveChart.SeriesCollection.NewSeries
   .Name = Sheet2.Range("A16")
   .Values = seriesRange
   .XValues = Sheet2.Range("A16:A18")
   .ApplyDataLabels
    End With

Why does the seriesRange appear empty in #2 and not in #1.
Hoping you can answer this last question in regard to your response.