Erstellen von PivotTables mit VBA

• Die Kombination von On Error Resume Next mit dem Umschalten von DisplayAlerts und .Delete ist ein sicheres Muster für "Löschen, falls vorhanden": Das Löschen eines nicht existierenden Arbeitsblatts würde normalerweise einen Fehler auslösen und das Makro anhalten, aber On Error Resume Next weist VBA an, diesen spezifischen Fehler stillschweigend zu überspringen; DisplayAlerts = False unterdrückt Excels eigene Bestätigungsabfrage "Möchten Sie dieses Blatt wirklich löschen?";
• Error GoTo 0 unmittelbar danach schaltet die normale Fehlerberichterstattung wieder ein — würde On Error Resume Next für den Rest des Sub aktiv bleiben, würden auch alle späteren, nicht zusammenhängenden Fehler stillschweigend ignoriert, was vermieden werden sollte;
• ThisWorkbook.PivotCaches.Create erstellt einen Schnappschuss der Tabellendaten — den PivotCache, nicht die PivotTable selbst —, welcher das Objekt ist, aus dem jede PivotTable im Hintergrund tatsächlich aufgebaut wird;
• pc.CreatePivotTable wandelt diesen Schnappschuss in eine sichtbare PivotTable um, die ab Zelle A3 auf dem neuen Pivot-Blatt platziert und mit dem Namen ptProfitByRegion versehen wird, sodass späterer Code (wie RefreshTable) sie wieder anhand des Namens finden kann;
• PivotFields("Region").Orientation = xlRowField und die darunterstehende Zeile für Month entsprechen im Code direkt dem Ziehen von Region in das Zeilenfeld und Month in das Spaltenfeld in der Feldliste;
• AddDataField befüllt den Wertebereich — das zweite Argument ("Sum of Profit") ist lediglich die von Excel angezeigte Spaltenüberschrift, und xlSum gibt an, dass die Werte addiert und nicht gemittelt oder gezählt werden.

Hier ist der Code, um eine sechste Region als neue März-Zeile in tblReports hinzuzufügen, wobei ListRows.Add verwendet wird, anstatt sie manuell einzutragen:

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

Führen Sie dies einmal aus und starten Sie dann das RefreshProfitPivot-Makro aus dem vorherigen Abschnitt – die Pivot-Tabelle sollte nun "Southwest" als neue Zeile neben North, South, East, West und Central anzeigen, ohne dass das Pivot-Erstellungs-Makro erneut angepasst werden musste.

1. Ausführen von BuildProfitPivot wie vorgegeben

• Kopieren Sie das Sub exakt wie im Kapitel angegeben in Ihr Modul und führen Sie es einmal aus.
• Überprüfen Sie den Projekt-Explorer oder Ihre Blattregister – ein neues Blatt mit dem Namen "Pivot" sollte erscheinen, mit Region in den Zeilen und Month in den Spalten, wobei Profit summiert wird.

2. Hinzufügen einer sechsten Region und nur aktualisieren

• Geben Sie die neue Zeile direkt im Arbeitsblatt ein (nicht per Code) – gehen Sie ans Ende von tblReports und fügen Sie eine März-Zeile für eine erfundene Region, z. B. "Southwest", hinzu.
• Führen Sie nicht erneut BuildProfitPivot aus – das würde das gesamte Pivot-Blatt löschen und neu erstellen, was dem Zweck dieser Übung widerspricht.
• Führen Sie stattdessen nur die einzeilige RefreshTable-Anweisung aus dem Kapitel aus – Sie müssen die bestehende PivotTable wie im Refresh-Beispiel des Kapitels beim Namen referenzieren.

3. Felder tauschen und zusammengefassten Wert ändern

• Drei Zeilen im With pt-Block müssen geändert werden: welches Feld als xlRowField, welches als xlColumnField und auf welches Feld sich AddDataField bezieht.
• Geben Sie dieser modifizierten Version einen anderen Sub-Namen und einen anderen TableName – die Wiederverwendung der gleichen Namen wie im Original würde entweder zu einem Fehler führen oder die erste Pivot-Tabelle stillschweigend überschreiben.
• Die Bezeichnung, die an AddDataField übergeben wird (das zweite Argument, z. B. "Sum of Profit"), ist nur Anzeigetext – passen Sie sie an das an, was Sie tatsächlich zusammenfassen.

Punkt 2 – nach dem manuellen Hinzufügen der sechsten Regionszeile führen Sie nur Folgendes aus:

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

Punkt 3 – eine separate, modifizierte Version:

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

Führen Sie BuildSalesPivotByMonth aus und Sie erhalten ein neues Blatt "SalesPivot" mit Month in den Zeilen, Region in den Spalten und Sales-Summen im Tabellenkörper – das Spiegelbild des ursprünglichen Layouts.