Excel VBA Pivot-Tabelle aktualisieren

Wenn wir eine Pivot-Tabelle in das Blatt einfügen, müssen sich die Pivot-Tabellendaten , sobald sich die Daten ändern, nicht manuell ändern. In VBA gibt es jedoch eine Anweisung zum Aktualisieren der Pivot-Tabelle, expression.refreshtable . Mit dieser Anweisung können wir die Pivot-Tabelle aktualisieren Pivot-Tabelle durch Verweisen auf das Arbeitsblatt, aus dem sie besteht, oder wir können auf die gesamten Pivot-Tabellen in den Arbeitsblättern verweisen und sie alle gleichzeitig aktualisieren.

Die Pivot-Tabelle ist für die Analyse der großen Datenmenge von entscheidender Bedeutung. Es hilft dabei, Daten zu analysieren, zusammenzufassen und nützliche Daten zu interpretieren. Eines der Probleme mit dieser Pivot-Tabelle ist jedoch, dass sie nicht automatisch aktualisiert wird, wenn sich die Quelldaten ändern. Der Benutzer muss die Pivot-Tabelle aktualisieren, indem er bei jeder Änderung zur jeweiligen Pivot-Tabelle wechselt. Aber verabschieden Sie sich vom manuellen Prozess, denn hier haben wir die Methode, die Pivot-Tabelle zu aktualisieren, sobald Sie Änderungen an der Pivot-Tabelle vornehmen.

Wie wird der VBA-Code für Pivot-Tabellendaten automatisch aktualisiert?

Die Pivot-Tabelle muss nur aktualisiert werden, wenn sich die Quelldaten der Pivot-Tabelle, auf die wir uns beziehen, ändern.

Schauen Sie sich zum Beispiel die folgenden Daten und die Pivot-Tabelle an.

Jetzt werde ich die Nummern in den Quelldaten ändern, dh von A1 nach B17.

In Zelle B9 muss ich den Wert von 499 auf 1499 ändern, dh 1000 Datenerhöhung, aber wenn Sie sich den Pivot ansehen, wird das Ergebnis immer noch als 4295 anstelle von 5295 angezeigt. Ich muss meine Pivot-Tabelle manuell aktualisieren, um die Pivot-Tabelle zu aktualisieren.

Um dieses Problem zu beheben, müssen wir einen einfachen Excel-Makrocode schreiben, um die Pivot-Tabelle zu aktualisieren, wenn sich die Quelldaten ändern.

Sie können diese VBA Refresh Pivot Table Excel-Vorlage hier herunterladen - VBA Refresh Pivot Table Excel-Vorlage

# 1 - Einfaches Makro zum Aktualisieren aller Tabellen

Schritt 1: Ereignis des Datenblatts ändern

Wir müssen das Änderungsereignis des Datenblattes auslösen. Doppelklicken Sie im Visual Basic-Editor auf das Datenblatt.

Sobald Sie auf das Blatt doppelklicken, wählen Sie "Arbeitsblatt" und wählen Sie das Ereignis als "Ändern".

Sie sehen eine automatische Unterprozedur, die als Worksheet_Change (ByVal Target As Range) geöffnet ist.

Schritt 2: Arbeitsblattobjekt verwenden

Lesen Sie das Datenblatt mithilfe des Arbeitsblattobjekts.

Schritt 3: Pivot-Tabelle nach Namen beziehen

Beziehen Sie sich auf den Namen der Pivot-Tabelle mit dem Namen der Pivot-Tabelle.

Schritt 4: Verwenden Sie die Methode "Tabelle aktualisieren"

Wählen Sie die Methode als "Tabelle aktualisieren".

Dieser Code aktualisiert nun die Pivot-Tabelle "PivotTable1", wenn sich das Quelldatenblatt ändert. Sie können den folgenden Code verwenden, Sie müssen nur den Namen der Pivot-Tabelle ändern.

Code:

 Private Sub Worksheet_Change (ByVal Ziel als Bereich) Arbeitsblatt ("Datenblatt"). PivotTables ("PivotTable1"). RefreshTable End Sub

# 2 - Aktualisieren Sie alle Pivot-Tabellen desselben Arbeitsblatts

Wenn Sie mehrere Pivot-Tabellen im selben Arbeitsblatt haben, können Sie alle Pivot-Tabellen mit einem einzigen Klick selbst aktualisieren. Verwenden Sie den folgenden Code, um alle Pivot-Tabellen im Blatt zu aktualisieren.

Code:

 Sub Refresh_Pivot_Tables_Example1 () Arbeitsblätter ("Datenblatt"). Mit ActiveSheet auswählen .PivotTables ("Tabelle1"). RefreshTable .PivotTables ("Table2"). RefreshTable .PivotTables ("Table3"). RefreshTable .PivotTables ("Table4"). RefreshTable .PivotTables ("Table5"). RefreshTable End With End Sub

Sie müssen den Namen des Arbeitsblatts und die Pivot-Tabellennamen gemäß Ihren Arbeitsblattdetails ändern.

# 3 - Aktualisieren Sie alle Tabellen in der Arbeitsmappe

Es ist sehr unwahrscheinlich, dass sich alle Pivot-Tabellen auf demselben Arbeitsblatt befinden. Normalerweise versuchen wir für jeden Bericht, separate Pivot-Tabellen in separaten Blättern hinzuzufügen. In diesen Fällen können wir den Code nicht für jede zu aktualisierende Pivot-Tabelle weiter schreiben.

Wir können also mit einem einzigen Code mithilfe von Schleifen alle Pivot-Tabellen in der Arbeitsmappe durchlaufen und sie mit einem einzigen Klick auf die Schaltfläche aktualisieren.

Der folgende Code durchläuft jede Pivot-Tabelle und aktualisiert sie.

Code 1:

 Sub Refresh_Pivot_Tables_Example2 () PT als PivotTable für jeden PT in ActiveWorkbook.PivotTables dimmen PT.RefreshTable Next PT End Sub

Code 2:

 Sub Refresh_Pivot_Tables_Example3 () PC als PivotCache für jeden PC in ActiveWorkbook.PivotCaches dimmen PC.Refresh Next PT End Sub

Beide Codes führen die Aktualisierung von Pivot-Tabellen durch.

Wenn Sie möchten, dass die Pivot-Tabelle aktualisiert wird, sobald sich das Datenblatt des Pivot-Blattes ändert, müssen Sie die obigen Codes kopieren und in das Arbeitsblattänderungsereignis in dieser Arbeitsmappe einfügen.

# 4 - Vermeiden Sie Ladezeiten, indem Sie das Ereignis zum Deaktivieren des Arbeitsblatts verwenden

Wenn wir das Ereignis "Arbeitsblattänderung" verwenden, wird es auch dann aktualisiert, wenn sich die Datenquelle nicht ändert, das Arbeitsblatt jedoch geändert wird.

Selbst wenn Sie einen einzelnen Punkt in das Arbeitsblatt eingeben, wird versucht, die Pivot-Tabelle zu aktualisieren. Um dies zu vermeiden, können wir anstelle der Methode "Arbeitsblattänderung" die Methode "Arbeitsblatt deaktivieren" verwenden.