Excel Macro in Excel to repeat for multiple charts and sheets

[AxilD]

My friend has set up an excel workbook with a sheet for every month (labelled Jan graphs, Feb graphs, Mar graphs etc) which has 7 pie charts per sheet. The problem is the pie charts are showing the 0% fields and the data labels are overlapping. I have found a macro that gets rid of the 0% labels but I need to repeat the macro for all the 7 charts on each sheet and all 12 worksheets for each month of the year. I am very new to VBA and coding of any type and have looked but can't find the solution.

The macro I am currently using for "Chart1" on "Jan graphs" is below. The charts are labelled Chart1, Chart2, Chart3 etc.


Sub ClearLabels()

Worksheets("Jan graphs").ChartObjects("Chart 1").Chart.ApplyDataLabels _
Type:=xlShowLabelAndPercent

For Each x In Worksheets("Jan graphs").ChartObjects("Chart 1"). _
Chart.SeriesCollection(1).Points

If InStr(x.DataLabel.Text, Chr(10) & "0%") > 0 Then
x.DataLabel.Delete
End If
Next
End Sub

 

[SlimJim]

The obvious solution would be to do an outer loop of the sheets with an inner loop of the charts. My experience with this (I am not a great, or even good, programmer) is that a spend a lot of time making it work the way I want.


I am assuming this is a one-off as described so why not do the obvious very inefficient method. Please ignore if elegance is your object!

Take the body of the macro

Worksheets("Jan graphs").ChartObjects("Chart 1").Chart.ApplyDataLabels _
Type:=xlShowLabelAndPercent

For Each x In Worksheets("Jan graphs").ChartObjects("Chart 1"). _
Chart.SeriesCollection(1).Points

If InStr(x.DataLabel.Text, Chr(10) & "0%") > 0 Then
x.DataLabel.Delete
End If
Next

add six copies and then change the chart labels to Chart2, Chart3, etc.

Now select the body of this new macro and copy it 11 times.

Select the copies one by one and do a replace for the month name. (If you are using the MS Visual Basic Editor then Ctrl-H will do a replace within selected text)