Criando Tabelas Dinâmicas com VBA

• On Error Resume Next combinado com a alternância de DisplayAlerts e .Delete é um padrão seguro de "excluir se existir": excluir uma planilha que não existe normalmente geraria um erro e interromperia a macro, mas On Error Resume Next instrui o VBA a continuar silenciosamente após esse erro específico; DisplayAlerts = False suprime o pop-up de confirmação do Excel "tem certeza de que deseja excluir esta planilha?";
• O Error GoTo 0 logo em seguida reativa a exibição normal de erros — deixar On Error Resume Next ativo pelo restante do Sub faria com que erros posteriores e não relacionados fossem ignorados silenciosamente, o que é uma armadilha a ser evitada;
• ThisWorkbook.PivotCaches.Create faz uma captura dos dados da Tabela — o PivotCache, não a própria PivotTable — que é o objeto a partir do qual toda PivotTable é realmente construída nos bastidores;
• pc.CreatePivotTable transforma essa captura em uma PivotTable visível, posicionada a partir da célula A3 na nova planilha Pivot e recebe o nome ptProfitByRegion para que códigos posteriores (como RefreshTable, por exemplo) possam encontrá-la pelo nome;
• PivotFields("Region").Orientation = xlRowField e a linha de Month logo abaixo são o equivalente direto em código de arrastar Region para a caixa de Linhas e Month para a caixa de Colunas na Lista de Campos;
• AddDataField é o que preenche a área de Valores — o segundo argumento ("Sum of Profit") é apenas o rótulo exibido pelo Excel como título da coluna, e xlSum indica que os valores devem ser somados em vez de fazer média ou contagem.

Aqui está o código para adicionar uma sexta região como uma nova linha de março em tblReports, usando ListRows.Add em vez de digitá-la manualmente:

Sub AddSixthRegion()
    Dim tbl As ListObject
    Dim newRow As ListRow

    Set tbl = ThisWorkbook.Worksheets("Reports").ListObjects("tblReports")
    Set newRow = tbl.ListRows.Add

    newRow.Range(1, 1).Value = "March"        ' Month
    newRow.Range(1, 2).Value = "Southwest"    ' Region
    newRow.Range(1, 3).Value = 41500          ' Sales
    newRow.Range(1, 4).Value = 28200          ' Expenses
    newRow.Range(1, 5).Value = 13300          ' Profit
    newRow.Range(1, 6).Value = 39000          ' Target
End Sub

Execute isso uma vez e, em seguida, execute a Sub RefreshProfitPivot mencionada anteriormente — a tabela dinâmica agora deve exibir "Southwest" como uma nova linha junto com North, South, East, West e Central, sem que você tenha alterado a macro de criação da tabela dinâmica.

1. Executando BuildProfitPivot como está

• Copie a Sub exatamente como está escrita no capítulo para o seu módulo e execute uma vez.
• Verifique o Project Explorer ou as abas da planilha — uma nova planilha chamada literalmente "Pivot" deve aparecer, com Region listada nas linhas e Month distribuído nas colunas, somando Profit.

2. Adicionando uma sexta região e apenas atualizando

• Digite a nova linha diretamente na planilha (não via código) — vá até o final de tblReports e adicione uma linha de março para uma região fictícia, por exemplo, "Southwest".
• Não execute novamente o BuildProfitPivot — isso excluiria e reconstruiria toda a planilha Pivot do zero, o que vai contra o objetivo deste exercício.
• Em vez disso, execute apenas a instrução de uma linha RefreshTable do capítulo — será necessário referenciar a PivotTable existente pelo nome, da mesma forma que o exemplo de atualização do capítulo.

3. Trocando campos e alterando o valor resumido

• Três linhas dentro do bloco With pt precisam ser alteradas: qual campo é xlRowField, qual é xlColumnField e para qual campo o AddDataField aponta.
• Dê a essa versão modificada um nome de Sub diferente e um TableName diferente — reutilizar os mesmos nomes do original pode causar erro ou sobrescrever silenciosamente a primeira tabela dinâmica.
• O rótulo passado para o AddDataField (o segundo argumento, como "Sum of Profit") é apenas texto de exibição — atualize para corresponder ao que está sendo resumido agora.

Ponto 2 — após adicionar manualmente a linha da sexta região, execute apenas isto:

Sub RefreshProfitPivot()
    ThisWorkbook.Worksheets("Pivot").PivotTables("ptProfitByRegion").RefreshTable
End Sub

Ponto 3 — uma versão modificada separada:

Sub BuildSalesPivotByMonth()
    Dim wsData As Worksheet, wsPivot As Worksheet
    Dim tbl As ListObject
    Dim pc As PivotCache
    Dim pt As PivotTable

    Set wsData = ThisWorkbook.Worksheets("Reports")
    Set tbl = wsData.ListObjects("tblReports")

    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets("SalesPivot").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    Set wsPivot = ThisWorkbook.Worksheets.Add
    wsPivot.Name = "SalesPivot"

    Set pc = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, SourceData:=tbl.Range)

    Set pt = pc.CreatePivotTable( _
        TableDestination:=wsPivot.Range("A3"), _
        TableName:="ptSalesByMonth")

    With pt
        .PivotFields("Month").Orientation = xlRowField
        .PivotFields("Region").Orientation = xlColumnField
        .AddDataField .PivotFields("Sales"), "Sum of Sales", xlSum
    End With
End Sub

Execute BuildSalesPivotByMonth e você deverá obter uma nova planilha "SalesPivot" com Month nas linhas, Region nas colunas e totais de Sales no corpo — o espelho do layout original.