1

I am trying to create a dynamic pie chart. The below does code does the job only for fixed ranges. My problem is I have to fetch the data from last used row and give it as the input for chart. And I cannot change the labels in chart.

Sub Chart()
With ActiveSheet.ChartObjects.Add(Left:=440, Width:=310, Top:=475, Height:=200)
With .Chart
    .SetSourceData Source:=ActiveSheet.Range("G16:K16")
    .ChartType = xl3DPie
    .HasTitle = True
    .ApplyDataLabels Type:=xlDataLabelsShowLabelAndPercent
    .ChartTitle.Characters.text = "Summary Report"
    .ChartTitle.Characters.Font.Size = 10
    .ChartTitle.Characters.Font.Bold = True
    .HasLegend = False
    .RightAngleAxes = True
    With .ChartArea
        With .Border
            .Weight = 2
            .LineStyle = 0
        End With
        With .Interior
            .ColorIndex = 39
            .PatternColorIndex = 1
            .Pattern = 1
        End With
    End With
    With .PlotArea
        With .Border
            .Weight = 2
            .LineStyle = 0
        End With
        With .Interior
            .ColorIndex = 39
            .PatternColorIndex = 1
            .Pattern = 1
        End With
    End With
End With
End With

End Sub

And one more thing is that I want place the chart exactly three rows after the last row

Community
  • 1
  • 1
taz
  • 95
  • 1
  • 1
  • 9
  • What do you mean by labels? the legend? the datapoint labels? any axis labels? Please by more specific. To place the chart 3 lines after the last data won't work, because the chart is placed on absolute coordinates of the sheet and the line height could be changed. So you have to calculate the positon of any anchor on the sheet to position the chart – Christine Ross May 28 '15 at 13:48
  • There are several ways to build a `Range` that uses the last used cell on a sheet. Search this site or refer [to this well written post](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba/11169920#11169920) on the topic. – Byron Wall May 28 '15 at 15:54
  • 1
    You could also set up a pivot table and create a pivot-chart and you should always have updated data. – Sam May 28 '15 at 18:13
  • I mean datapoint labels @ Ross – taz May 29 '15 at 06:19
  • As suggested by SammyB, I'd create a pivot-chart from a pivot table and use the dynamic range `=OFFSET(SheetName!$G$1,0,0,COUNTA(SheetName!$G:$G),5)`. Then you can just add a `.refresh` line to the code to refresh. Creating from scratch every time is unnecessary and awkward. – tonester640 Jun 04 '15 at 10:02

0 Answers0