🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA Pivot Table in Excel — erstellen, aktualisieren und warum sie alte Zahlen zeigt

|

VBA Pivot Table in Excel — erstellen, aktualisieren und warum sie alte Zahlen zeigt

TL;DR — Eine PivotTable ist auf einem PivotCache aufgebaut, einer eingefrorenen Kopie der Quelldaten. Bauen Sie eine in zwei Schritten: PivotCaches.Create(xlDatabase, source), dann .CreatePivotTable(destination). Ordnen Sie den Bericht an, indem Sie die .Orientation jedes Felds setzen (xlRowField, xlColumnField, xlPageField), und fügen Sie Zahlen mit AddDataField hinzu. Die Falle, die jeden erwischt: Sie ändern die Quelle, und die Pivot zeigt weiter die alten Zahlen, bis Sie pt.RefreshTable aufrufen. Richten Sie den Cache auf eine Table, und die Pivot wächst mit Ihren Daten, statt bei einem festen Bereich einzufrieren.

Dim pc As PivotCache, pt As PivotTable

Set pc = ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, SourceData:="tblSales")     ' ein Table-Name -> waechst von allein
Set pt = pc.CreatePivotTable( _
    TableDestination:=Worksheets("Report").Range("A3"), TableName:="pt_Sales")

pt.PivotFields("Region").Orientation = xlRowField
pt.AddDataField pt.PivotFields("Amount"), "Total Amount", xlSum

pt.RefreshTable                                          ' nach jeder Aenderung die Quelle neu lesen

Jeder, der Berichte automatisiert, schreibt irgendwann ein Makro, das eine PivotTable baut, und läuft dann gegen dieselbe Wand: Die Pivot stimmt an dem Tag, an dem Sie sie bauen, und ist an jedem Tag danach falsch. Sie fügen hundert Zeilen Umsatz hinzu, lassen den Bericht laufen, und die Summen haben sich nicht bewegt. Nichts hat einen Fehler geworfen. Die Ursache ist die eine Idee, die es über Pivots festzuhalten lohnt: Eine PivotTable fasst nicht Ihre Zellen zusammen — sie fasst einen PivotCache zusammen, eine Kopie der Daten, angefertigt zur Bauzeit. Jede Eigenheit weiter unten folgt daraus: warum Sie aktualisieren, warum der Quellbereich zählt, warum sich zwei Pivots einen Cache teilen können. Halten Sie „sie liest eine Momentaufnahme, nicht das Blatt" fest, und die Pivot hört auf, Sie zu überraschen.

Was Sie lernen

  • Das mentale Modell — eine Pivot liest einen PivotCache, nicht Ihre lebenden Zellen
  • Der moderne zweistufige Aufbau: PivotCaches.Create, dann CreatePivotTable
  • Felder über die vier Orientation-Werte anordnen und Datenfelder hinzufügen
  • Die Aktualisierungsfalle — warum Ihre Pivot alte Zahlen zeigt und wie Sie sie beheben
  • Die Quellbereichsfalle — den Cache auf eine Table richten, damit die Pivot wächst
  • Pivots finden, umrichten und entfernen, die eine Arbeitsmappe gesammelt hat

Das mentale Modell: eine Pivot liest einen Cache, nicht Ihre Zellen

Wenn Sie eine PivotTable erstellen, verdrahtet Excel sie nicht mit Ihrem Arbeitsblatt. Es nimmt die Quelldaten, kopiert sie in eine verborgene In-Memory-Struktur namens PivotCache, und die sichtbare Pivot ist nur eine Ansicht über diesen Cache. Der Cache ist eine Fotografie, aufgenommen in dem Moment, in dem Sie sie erstellt haben. Bearbeiten Sie die Quelle danach, ändert sich die Fotografie nicht — genau deshalb zeigt die Pivot weiter alte Zahlen.

Debug.Print pt.PivotCache.SourceData     ' woraus der Cache gebaut wurde
Debug.Print pt.PivotCache.RecordCount    ' wie viele Zeilen die MOMENTAUFNAHME haelt - nicht das Blatt

Zwei nützliche Folgen ergeben sich direkt daraus. Erstens können sich mehrere Pivots einen Cache teilen (bauen Sie die zweite aus pt.PivotCache statt aus einem frischen PivotCaches.Create), was die Datei klein hält und beide zusammen aktualisiert. Zweitens ist das Aktualisieren keine optionale Pflege — es ist der Schritt, der die Fotografie neu aufnimmt. Sobald Sie den Cache als eigenes Objekt mit einer eigenen Kopie der Daten sehen, hört der Rest der API auf, ein Sammelsurium von Methoden zu sein, und wird zu einer Geschichte: den Cache bauen, die Ansicht formen, die Momentaufnahme neu aufnehmen.

Eine PivotTable bauen: erst der Cache, dann die Tabelle

Das verlässliche moderne Muster besteht aus zwei ausdrücklichen Schritten. Erstellen Sie den Cache aus der Quelle, dann die Tabelle aus dem Cache:

Dim pc As PivotCache, pt As PivotTable

Set pc = ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:="Sales!A1:D1000")

Set pt = pc.CreatePivotTable( _
    TableDestination:=Worksheets("Report").Range("A3"), _
    TableName:="pt_Sales")

SourceData akzeptiert einen Table-Namen ("tblSales"), einen definierten Namen oder eine blattqualifizierte Adresse als Zeichenkette — und die Adressform ist der Ort, an dem die Quellbereichsfalle lauert, auf die wir weiter unten zurückkommen. Das TableDestination muss eine echte Zelle auf einem Arbeitsblatt sein, und die Pivot braucht Platz zum Wachsen, richten Sie sie also auf die obere linke Ecke eines ansonsten leeren Bereichs. Der Pivot einen Namen zu geben (TableName:="pt_Sales") zählt: So erreichen Sie sie in einem späteren Lauf wieder mit Worksheets("Report").PivotTables("pt_Sales"), statt bei PivotTables(1) zu raten.

Sie sehen vielleicht noch alte Makros, die Worksheets("Report").PivotTableWizard ... in einem einzigen Aufruf nutzen. Es funktioniert, aber es verbirgt den Cache, gibt Ihnen keinen sauberen Griff darauf und ist genau der Code, den der Makrorekorder ausspuckt. Bevorzugen Sie die zweistufige Form, damit der Cache — das Objekt, das Ihre Daten tatsächlich hält — etwas ist, das Sie benennen und aktualisieren können.

Die Felder anordnen: die vier Orientierungen

Eine PivotTable hat vier Zonen, und jedes Feld landet über seine .Orientation in einer davon. Zeilen- und Spaltenfelder werden zum Gitter; das Seitenfeld ist der Berichtsfilter; Datenfelder sind die zusammengefassten Zahlen:

With pt
    .PivotFields("Region").Orientation = xlRowField
    .PivotFields("Month").Orientation = xlColumnField
    .PivotFields("Category").Orientation = xlPageField     ' der Berichtsfilter
    .AddDataField .PivotFields("Amount"), "Total Amount", xlSum
End With

Zwei Dinge beißen hier. Erstens muss ein Feldname exakt der Quellüberschrift entsprechen — PivotFields("Ammount") wirft einen Laufzeitfehler, keine leere Spalte. Zweitens, und heimtückischer: Die Standardzusammenfassung für ein Datenfeld ist nur dann Summe, wenn die ganze Spalte numerisch ist. Rutscht ein einziger Textwert oder eine einzige Leerzelle in eine Betragsspalte, wechselt Excel den Standard stillschweigend auf Anzahl, und Ihr Bericht zeigt „wie viele", wo Sie „wie viel" erwartet haben. Deshalb schlägt das Hinzufügen von Datenfeldern mit einem ausdrücklichen AddDataField ... , xlSum das bloße Hineinziehen und Hoffen — Sie geben die Funktion an, statt eine Vermutung zu erben. Nutzen Sie xlAverage, xlCount, xlMax und den Rest genauso.

Die Aktualisierungsfalle: warum Ihre Pivot alte Zahlen zeigt

Das ist der Pivot-Bug Nummer eins, und es ist gar kein Bug — es ist der Cache, der seine Arbeit tut. Sie ändern die Quelle, der Cache hält noch die alte Fotografie, und die Pivot zeigt sie getreulich an:

' Sie haben 200 Zeilen an die Quelle angehaengt... die Pivot hat es nicht bemerkt.
pt.RefreshTable            ' DIESE Pivot aus ihrer Quelle neu lesen

pt.PivotCache.Refresh      ' den Cache aktualisieren -> jede Pivot, die ihn teilt
ThisWorkbook.RefreshAll    ' alle Pivots, Abfragen und Verknuepfungen der Datei

RefreshTable nimmt die Momentaufnahme für eine Pivot neu auf. Teilen sich mehrere Pivots einen Cache, aktualisiert PivotCache.Refresh sie alle auf einmal. Die praktische Regel: Jedes Makro, das die Daten ändert, muss die Pivots aktualisieren, die sie lesen, und jede Pivot, auf die Sie sich unbeaufsichtigt verlassen, sollte beim Öffnen der Arbeitsmappe aktualisieren. Eine Pivot, die einmal gebaut und nie aktualisiert wird, ist ein Screenshot, kein Bericht — sie zeigt weiter den Tag, an dem sie geboren wurde. Wenn Sie je ein „lebendes" Dashboard verschickt haben, das sich als eine Woche alt herausstellte, ist das der Grund.

Die Quellbereichsfalle: den Cache auf eine Table richten

Das zweite stille Versagen ist der Quellbereich. Bauen Sie den Cache aus einer festen Adresse, und die Zeilen des nächsten Monats fallen aus ihm heraus — selbst eine Aktualisierung holt sie nicht herein, weil sie nicht in dem Bereich liegen, den der Cache lesen sollte:

' FRAGIL - neue Zeilen unter Zeile 1000 sind fuer immer unsichtbar
Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, "Sales!A1:D1000")

' ROBUST - eine Table dehnt sich aus, der Cache sieht also immer jede Zeile
Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, "tblSales")

Den Cache auf eine Table (ein ListObject) zu richten, ist die Lösung, die alles Nachgelagerte selbstwartend macht: Die Table wächst, wenn Sie Zeilen hinzufügen, der Cache liest die ganze Table, und ein schlichtes RefreshTable holt die neuen Daten ohne Bereichsrechnerei herein. Ein dynamischer benannter Bereich leistet dasselbe, wenn Sie keine Table nutzen können. Um eine bestehende Pivot ohne Neuaufbau auf eine bessere Quelle umzurichten, geben Sie ihr mit ChangePivotCache einen neuen Cache. Siehe VBA Table für das Umwandeln eines Bereichs in eine Table und VBA Named Range für die Alternative des dynamischen Namens.

Pivots finden, umrichten und entfernen

Pivots häufen sich an, und ein späterer Lauf muss die finden, die er erzeugt hat, statt eine zweite Kopie obendrauf zu stapeln. Durchlaufen Sie die Collections, um sie zu lokalisieren oder aufzuräumen:

Dim ws As Worksheet, pt As PivotTable
For Each ws In ThisWorkbook.Worksheets
    For Each pt In ws.PivotTables
        Debug.Print ws.Name & " ! " & pt.Name & " -> " & pt.PivotCache.SourceData
    Next pt
Next ws

Prüfen Sie vor dem Neuaufbau, ob Ihre benannte Pivot bereits existiert, und aktualisieren Sie sie, statt ein Duplikat hinzuzufügen; wollen Sie wirklich eine frische, löschen Sie die alte mit pt.TableRange2.Clear (der ganze Pivot-Bereich), damit ein neuer Aufbau sauberen Platz hat. Behandeln Sie die Pivot als dauerhaftes Objekt, das Sie über den Namen nachschlagen — dieselbe Disziplin, die VBA Worksheet-Code davon abhält, in den falschen Tab zu schreiben, hält Ihr Berichtsmakro davon ab, bei jedem Lauf Pivots zu züchten.

Wie ExcelMaster hilft

Die Pivot-Fehler, die echte Zeit kosten, sind die stillen: der Bericht, der nie aktualisiert wurde, der Quellbereich, der im März bei Zeile 1000 aufhörte, die Summe, die still zu einer Anzahl wurde, weil eine Zelle Text enthielt. Jeder läuft ohne Fehler und reicht jemandem die falsche Zahl.

ExcelMaster baut Pivots so, wie es ein sorgfältiger Analyst täte. Bitten Sie es, „den Umsatz nach Region und Monat zusammenzufassen", und es richtet den Cache auf eine Table, damit die Quelle von allein wächst, setzt die Zusammenfassungsfunktion jedes Datenfelds ausdrücklich, statt Anzahl zu erben, aktualisiert nach dem Schreiben und schlägt die Pivot über den Namen nach, sodass ein erneuter Lauf den Bericht aktualisiert, statt ihn zu duplizieren. Sie beschreiben die gewünschte Zusammenfassung; es verdrahtet den Cache, die Felder und die Aktualisierung, sodass die Zahl auch nächsten Monat noch stimmt.

Häufig gestellte Fragen

Wie erstelle ich eine PivotTable in VBA?

Bauen Sie sie in zwei Schritten. Erstellen Sie zuerst einen Cache aus der Quelle: Set pc = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="tblSales"). Dann die Tabelle aus dem Cache: Set pt = pc.CreatePivotTable(TableDestination:=Worksheets("Report").Range("A3"), TableName:="pt_Sales"). Richten Sie SourceData auf einen Table-Namen, damit die Pivot mit Ihren Daten wächst, und geben Sie der Pivot einen Namen, damit Sie sie wiederfinden.

Warum aktualisiert sich meine VBA-PivotTable nicht, wenn sich die Daten ändern?

Weil eine PivotTable einen PivotCache liest — eine eingefrorene Kopie der Quelle, die beim Erstellen entstand — nicht Ihre lebenden Zellen. Die Quelle zu ändern rührt den Cache nicht an, also zeigt die Pivot weiter die alten Zahlen. Rufen Sie pt.RefreshTable auf, um eine Pivot neu zu lesen, pt.PivotCache.Refresh für jede Pivot, die den Cache teilt, oder ThisWorkbook.RefreshAll für die ganze Datei nach jeder Änderung.

Wie füge ich in VBA Felder zu einer PivotTable hinzu?

Setzen Sie die Orientation jedes Felds: pt.PivotFields("Region").Orientation = xlRowField, dann xlColumnField und xlPageField für die Spalten- und Filterzone. Fügen Sie Zahlen mit pt.AddDataField pt.PivotFields("Amount"), "Total Amount", xlSum hinzu, damit Sie die Zusammenfassungsfunktion steuern — sonst rät Excel und nimmt standardmäßig Anzahl, wenn die Spalte irgendeinen Text oder eine Leerzelle enthält.

Wie verhindere ich, dass der Pivot-Quellbereich neue Zeilen verpasst?

Richten Sie den Cache nicht auf eine feste Adresse wie "Sales!A1:D1000", denn darunter hinzugefügte Zeilen werden nie gesehen. Wandeln Sie die Quelle in eine Table um und übergeben Sie den Table-Namen als SourceData (PivotCaches.Create(xlDatabase, "tblSales")); die Table dehnt sich automatisch aus, sodass ein normales RefreshTable jede neue Zeile aufnimmt. Ein dynamischer benannter Bereich funktioniert auch, wenn eine Table keine Option ist.

Wie durchlaufe ich alle PivotTables in einer Arbeitsmappe?

Verschachteln Sie zwei Schleifen: For Each ws In ThisWorkbook.Worksheets, dann For Each pt In ws.PivotTables. Darin sagen Ihnen pt.Name und pt.PivotCache.SourceData, welche Pivot was liest. Nutzen Sie dieselbe Schleife, um jede Pivot zu aktualisieren, oder um zu prüfen, ob Ihre benannte Pivot bereits existiert, bevor Sie ein Duplikat bauen.

Getestet in

Getestet in: Excel 365 (Windows 11), VBA 7.1 — zuletzt geprüft am 02.09.2026.

Verwandte Anleitungen: VBA Table · VBA Chart · VBA Range · VBA Named Range · VBA Worksheet