W
Walter
For some reason my code keeps grabbing my entire block of data instead of the
last 6 rows. I have captured the cell addresses in variables and am trying
to capture the new ranges of data in the syntax for the chart. Not sure
where my problem resides. My data will expand by one row every week. The
headings are in cell B20 and C20. The chart code is not grabbing
discontinuous data but all 24 rows when I only want the heading row plus 6
rows of data.
Here is my code:
Set rngBurndownData = shtTotalData.Range("$A20").CurrentRegion
DateAddress = Cells(Rows.Count, "A").End(xlUp).Address
shtTotalData.Range(DateAddress).Select
ActiveCell.Offset(0, 2).Select
LastRngCell = ActiveCell.Address 'bottom right hand range cell in last
column
ActiveCell.Offset(-5, -2).Select
AcolDateAddr = ActiveCell.Address 'first cell in range A column
Charts.Add
ActiveChart.ChartType = xlColumnStacked
ActiveChart.SetSourceData Source:=Sheets("Total Data").Range( _
"$A$20:$C$20", AcolDateAddr & ":" & LastRngCell), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
Any help is greatly appreciated!
last 6 rows. I have captured the cell addresses in variables and am trying
to capture the new ranges of data in the syntax for the chart. Not sure
where my problem resides. My data will expand by one row every week. The
headings are in cell B20 and C20. The chart code is not grabbing
discontinuous data but all 24 rows when I only want the heading row plus 6
rows of data.
Here is my code:
Set rngBurndownData = shtTotalData.Range("$A20").CurrentRegion
DateAddress = Cells(Rows.Count, "A").End(xlUp).Address
shtTotalData.Range(DateAddress).Select
ActiveCell.Offset(0, 2).Select
LastRngCell = ActiveCell.Address 'bottom right hand range cell in last
column
ActiveCell.Offset(-5, -2).Select
AcolDateAddr = ActiveCell.Address 'first cell in range A column
Charts.Add
ActiveChart.ChartType = xlColumnStacked
ActiveChart.SetSourceData Source:=Sheets("Total Data").Range( _
"$A$20:$C$20", AcolDateAddr & ":" & LastRngCell), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
Any help is greatly appreciated!