Macros for multiple graphs

I currently have a 70 page spreadsheet with similar data that needs representing in pie chart format. All the column headers are the same and I only need 2 columns (A and T). There are however different numbers of rows. Is it possible to write a macro to detect the number of rows before it draws the relevant pie chart (I've managed the pie chart bit!!)

See attached example, same concept but cant post real data.

Cheers

Jeremy

Hello Jeremy,

Add a Standard VBA Module to your workbook. Copy and paste the code below into it. This should get you going.

Sub SetupChartSeries()

  Dim LastRow As Long
  Dim SourceRng As Range
  
  With ActiveSheet
    If .ChartObjects.Count = 0 Then
       MsgBox "There are no Charts on this worksheet.", vbExclamation + vbOKOnly
       Exit Sub
    End If
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    Set SourceRng = .Range("A1:B" & LastRow)
      With .ChartObjects(1)
        .Chart.SetSourceData SourceRng
        .Chart.ApplyDataLabels Type:=xlDataLabelsShowLabelAndPercent
      End With
  End With
  
End Sub

Sincerely,
Leith Ross

I have pasted as suggested, but it just comes up with the dialogue box saying no chart objects. What do I have to change to get the pie charts to draw when I run the macro?? I have assigned macro to the smilie button in the chart toolbar.

Cheers

Jez

Hello Jeremy,

I wasn't sure if you wanted the macro to create the charts. Form your post you seemed to iindicate that you had the charts created. No problem. I can easilty change the macro to create the charts also.

Sincerely,
Leith Ross

I have recorded a macro for drawing a pie chart but I am not sure which bits of code I need to keep and which to delete when I tie it in with what you have sent me?

See attached

regards

Jez

Hello Jeremy,

Copy this code into your macro module. If the chart doesn't exisit, it will create it. If you want to change the Pie Chart format, let me know.

Sub SetupChartSeries()

  Dim LastRow As Long
  Dim SourceRng As Range
  Dim ChartPlotArea As PlotArea
  
  With ActiveSheet
    If .ChartObjects.Count = 0 Then
       CreatePieChart
    End If
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    Set SourceRng = .Range("A1:B" & LastRow)
      With .ChartObjects(1).Chart
        .SetSourceData SourceRng
        .ApplyDataLabels Type:=xlDataLabelsShowLabelAndPercent
        Set ChartPlotArea = .PlotArea
      End With
      With ChartPlotArea
        .Border.ColorIndex = 2
        .Interior.ColorIndex = 2
      End With
  End With
  
End Sub

Private Sub CreatePieChart()

  Dim NewChart As ChartObject
  
    Set NewChart = ActiveSheet.ChartObjects.Add(200, 100, 500, 300)
    
    With NewChart.Chart
      .ChartType = xlPie
    End With
            
End Sub

Sincerely,
Leith Ross