Crie um gráfico dinâmico no Excel via VBA

Question

Estou tentando criar um gráfico, manualmente ou com o VBA, que será atualizado automaticamente quando novas folhas forem adicionadas. Os dados são importados de uma tabela externa mensalmente e cada vez são adicionados a uma nova planilha. A estrutura de dados é sempre a mesma.

Eu sei usar gráficos dinâmicos se eu quiser que ele seja atualizado para novas linhas e colunas, mas isso é diferente.

Eu tentei usar o gravador de macro junto com o Assistente de gráfico dinâmico e, em seguida, modificá-lo com um loop for, assim:

For i = 1 To Sheets.Count
ActiveWorkbook.PivotCaches.Create(SourceType:=xlConsolidation, SourceData:= _
Array("Sheet" & i & "!R1C1:R10C7", "Item1"), _
Version:=6).CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=6

More Code

Next i

O restante do código configura uma soma de valores e assim por diante.

Eu vejo que há um método PivotCaches.Item, mas não vejo nada sobre a atualização de um gráfico dinâmico existente.

Isso não precisa ser um gráfico dinâmico, se um gráfico regular funcionasse. Da mesma forma, se houver uma maneira de fazer isso sem o VBA, também é bom.

EDIT: Acho que resolvi. Quaisquer críticas são bem vindas.

For i = 1 To Sheets.Count
If IsEmpty(pivotArray) Then
    pivotArray = Array(1)
    ReDim Preserve pivotArray(1 To UBound(pivotArray) + 1) As Variant
    pivotArray(1) = Array("Sheet" & i & "!R1C1:R4C3", "Item1")
Else
    ReDim Preserve pivotArray(1 To UBound(pivotArray) + 1) As Variant
    pivotArray(UBound(pivotArray)) = Array("Sheet" & i & "!R1C1:R4C3", "Item1")
End If
Next i