Animated charts

Hi,

I have my data with a total. The total depends on the amount of data considered. I want to present a chart evolution, with one data, then with the total of two data, then 3...

I already manage to change the total calculation in this way with a macro, but my chart does not change with the table, but only after the macro ends.

Could someone give me some ideas please, I'm new to VBA, I added some delay and refresh to the loop but it didn't help.

Rgds,

Carlos.


My data:

A B C D E F G
1 item 5
2
3 id 1 2 3 4 5 total
4 a 1 3 2 0 0 6
5 b 0 3 0 0 3 6
6 c 1 1 2 0 2 6
7 d 0 3 1 2 2 8
8 e 1 1 2 1 3 8


the total formula in G4:

=SUM(OFFSET(B4;0;0;1;MATCH($B$1;$B$3:$F$3;0)))


The chart is clustered bar with series "id" and "total"

the macro:

Sub mychart()
Dim i As Byte
    For i = 1 To 5
    Range("B1").Value = i
    Application.Wait (Now + TimeValue("0:00:02"))
    ActiveWorkbook.RefreshAll
    Next
End Sub