Excel Macro in Excel to repeat for multiple charts and sheets


Joined
Jan 15, 2008
Messages
1
Reaction score
0
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
 
Ad

Advertisements

Joined
Feb 3, 2006
Messages
146
Reaction score
0
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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top