Wie man Auto Refresh Pivot-Tabellen mit VBA Excel
Wie wir alle wissen, werden Änderungen an den Quelldaten einer Pivot-Tabelle nicht sofort in der Pivot-Tabelle angezeigt. Wir müssen die Pivot-Tabellen aktualisieren, um die Änderungen zu sehen. Und wenn Sie eine aktualisierte Datei senden, ohne die Pivot-Tabellen zu aktualisieren, ist dies möglicherweise peinlich. In diesem Artikel erfahren Sie, wie Sie eine Pivot-Tabelle mithilfe von VBA automatisch aktualisieren. Dieser Weg ist einfacher als Sie sich vorgestellt haben.
Dies ist die einfache Syntax zum automatischen Aktualisieren von Pivot-Tabellen in der Arbeitsmappe.
'Code in Source Data Sheet Object
Private Sub Worksheet_Deactivate()
sheetname_of_pivot_table.PivotTables("pivot_table_name").PivotCache.Refresh
End Sub
Was sind Pivot-Caches? Jede Pivot-Tabelle speichert die Daten im Pivot-Cache. Aus diesem Grund kann Pivot frühere Daten anzeigen. Wenn wir Pivot-Tabellen aktualisieren, wird der Cache mit neuen Quelldaten aktualisiert, um die Änderungen in der Pivot-Tabelle widerzuspiegeln.
Wir brauchen also nur ein Makro, um den Cache der Pivot-Tabellen zu aktualisieren. Wir werden dies über einen Link tun: / events-in-vba-das-Arbeitsblatt-Ereignisse-in-excel-vba [Arbeitsblatt-Ereignis], damit wir das Makro nicht manuell ausführen müssen. === Wo Code zum automatischen Aktualisieren von Pivot-Tabellen? Wenn sich Ihre Quelldaten und Pivot-Tabellen in verschiedenen Arbeitsblättern befinden, sollte der VBA-Code in das Quelldatenblatt aufgenommen werden.
Hier verwenden wir das Worksheet_SelectionChange-Ereignis. Dadurch wird der Code immer dann ausgeführt, wenn wir vom Quelldatenblatt zu einem anderen Blatt wechseln.
Ich werde später erklären, warum ich dieses Ereignis verwendet habe.
Hier habe ich Quelldaten in Blatt2 und Pivot-Tabellen in Blatt1.
Öffnen Sie VBE mit STRG + F11. Im Projektexplorer sehen Sie drei Objekte, Sheet1, Sheet2 und die Arbeitsmappe.
Da Sheet2 die Quelldaten enthält, doppelklicken Sie auf das Objekt sheet2.
Jetzt sehen Sie oben im Codebereich zwei Dropdowns. Wählen Sie aus der ersten Dropdown-Liste das Arbeitsblatt aus. Und aus der zweiten Dropdown-Liste wählen Sie Deaktivieren. Dadurch wird ein leerer Subname Worksheet_Deactivate eingefügt. Unser Code wird in dieses Sub geschrieben. Alle in diesem Unterabschnitt geschriebenen Zeilen werden ausgeführt, sobald der Benutzer von diesem Blatt zu einem anderen Blatt wechselt.
Auf Blatt 1 habe ich zwei Pivot-Tabellen. Ich möchte nur eine Pivot-Tabelle aktualisieren. Dafür muss ich den Namen der Pivot-Tabelle kennen. Um den Namen einer Pivot-Tabelle zu ermitteln, wählen Sie eine beliebige Zelle in dieser Pivot-Tabelle aus und wechseln Sie zur Registerkarte Pivot-Tabellenanalyse. Auf der linken Seite sehen Sie den Namen der Pivot-Tabelle. Hier können Sie auch den Namen der Pivot-Tabelle ändern.
Nachdem wir den Namen der Pivot-Tabelle kennen, können wir eine einfache Zeile schreiben, um die Pivot-Tabelle zu aktualisieren.
Private Sub Worksheet_Deactivate()
Sheet1.PivotTables("PivotTable1").PivotCache.Refresh
End Sub
Und es ist geschafft.
Wenn Sie jetzt von den Quelldaten wechseln, wird dieser VBA-Code ausgeführt, um die Pivot-Tabelle1 zu aktualisieren. Wie Sie im GIF unten sehen können.
Wie aktualisiere ich alle Pivot-Tabellen in der Arbeitsmappe? Im obigen Beispiel wollten wir nur eine bestimmte Pivot-Tabelle aktualisieren. Wenn Sie jedoch alle Pivot-Tabellen in einer Arbeitsmappe aktualisieren möchten, müssen Sie nur geringfügige Änderungen an Ihrem Code vornehmen.
Private Sub Worksheet_Deactivate()
'Sheet1.PivotTables("PivotTable1").PivotCache.Refresh
For Each pc In ThisWorkbook.PivotCaches
pc.Refresh
Next pc
End Sub
In diesem Code verwenden wir einen For loop, um die einzelnen Pivot-Caches in der Arbeitsmappe zu durchlaufen. Das ThisWorkbook-Objekt enthält alle Pivot-Caches. Um darauf zuzugreifen, verwenden wir ThisWorkbook.PivotCaches.
Warum Worksheet_Deactivate Event verwenden?
Wenn Sie die Pivot-Tabelle aktualisieren möchten, sobald Änderungen an den Quelldaten vorgenommen werden, sollten Sie das Ereignis Worksheet_Change verwenden. Aber ich empfehle es nicht. Dadurch wird in Ihrer Arbeitsmappe der Code jedes Mal ausgeführt, wenn Sie Änderungen am Blatt vornehmen. Möglicherweise müssen Sie Hunderte von Änderungen vornehmen, bevor Sie das Ergebnis sehen möchten. Excel aktualisiert jedoch die Pivot-Tabelle bei jeder Änderung. Dies führt zu einer Verschwendung von Verarbeitungszeit und Ressourcen. Wenn Sie also Pivot-Tabellen und -Daten in verschiedenen Arbeitsblättern haben, ist es besser, das Arbeitsblatt-Deaktivierungsereignis zu verwenden. Damit können Sie Ihre Arbeit beenden. Sobald Sie zu Pivot-Tabellenblättern wechseln, um die Änderungen anzuzeigen, werden die Änderungen geändert.
Wenn sich Pivot-Tabellen und Quelldaten auf demselben Blatt befinden und Pivot-Tabellen automatisch aktualisiert werden sollen, können Sie `link: / tips-using-worksheet-change-event-to-run-macro-when- verwenden Jede Änderung wird vorgenommen [Worksheet_Change Event] `.
Private Sub Worksheet_Change(ByVal Target As Range)
Sheet1.PivotTables("PivotTable1").PivotCache.Refresh
End Sub
Wie aktualisiere ich alles in Arbeitsmappen, wenn Änderungen an den Quelldaten vorgenommen werden? Wenn Sie alles in einer Arbeitsmappe aktualisieren möchten (Diagramme, Pivot-Tabellen, Formeln usw.), können Sie den Befehl ThisWorkbook.RefreshAll verwenden.
Private Sub Worksheet_Change(ByVal Target As Range) ThisWorkbook.RefreshAll End Sub
Bitte beachten Sie, dass dieser Code die Datenquelle nicht ändert. Wenn Sie also Daten unterhalb der Quelldaten hinzufügen, enthält dieser Code diese Daten nicht automatisch. Sie können link: / table-excel-2007-17-erstaunliche-Funktionen-von-Excel-Tabellen [Excel-Tabellen] verwenden, um Quelldaten zu speichern. Wenn Sie keine Tabellen verwenden möchten, können wir VBA auch zum Einfügen neuer Daten verwenden. Wir werden es im nächsten Tutorial lernen.
Also ja, Kumpel, so können Sie die Pivot-Tabellen in Excel automatisch aktualisieren. Ich hoffe, ich war erklärend genug und dieser Artikel hat Ihnen gute Dienste geleistet. Wenn Sie Fragen zu diesem Thema haben, können Sie mich im Kommentarbereich unten fragen.
Verwandte Artikel:
Dynamisches Aktualisieren des Datenquellenbereichs für Pivot-Tabellen in Excel: Um den Quelldatenbereich von Pivot-Tabellen dynamisch zu ändern, verwenden wir Pivot-Caches. Diese wenigen Zeilen können jede Pivot-Tabelle dynamisch aktualisieren, indem sie den Quelldatenbereich ändern. Verwenden Sie in VBA Pivot-Tabellen-Objekte wie unten gezeigt …
link: / events-in-vba-run-macro-if-any-change-made-on-sheet-range [Makro ausführen, wenn Änderungen am Blatt im angegebenen Bereich vorgenommen werden]: * In Ihren VBA-Praktiken würden Sie dies tun Sie müssen Makros ausführen, wenn sich ein bestimmter Bereich oder eine bestimmte Zelle ändert. In diesem Fall verwenden wir das Änderungsereignis, um Makros auszuführen, wenn eine Änderung an einem Zielbereich vorgenommen wird.
link: / tips-using-worksheet-change-event-to-run-macro-wenn-eine-Änderung vorgenommen wird [Makro ausführen, wenn eine Änderung auf dem Blatt vorgenommen wird] | Um Ihr Makro bei jeder Aktualisierung des Arbeitsblatts auszuführen, verwenden wir die Arbeitsblattereignisse von VBA.
link: / events-in-vba-einfachster-vba-code zum hervorheben der aktuellen zeile und spalte mit [Einfachster VBA-Code zum Hervorheben der aktuellen Zeile und Spalte mit] | Verwenden Sie dieses kleine VBA-Snippet, um die aktuelle Zeile und Spalte des Blattes hervorzuheben.
link: / events-in-vba-das-arbeitsblatt-events-in-excel-vba [Die Arbeitsblattereignisse in Excel VBA] | Das Arbeitsblattereignis ist sehr nützlich, wenn Ihre Makros ausgeführt werden sollen, wenn ein bestimmtes Ereignis auf dem Blatt auftritt.
Beliebte Artikel:
link: / Tastatur-Formel-Verknüpfungen-50-Excel-Verknüpfungen-zur-Steigerung-Ihrer-Produktivität [50 Excel-Verknüpfungen zur Steigerung Ihrer Produktivität] | Werden Sie schneller bei Ihrer Aufgabe. Mit diesen 50 Verknüpfungen können Sie noch schneller in Excel arbeiten.
link: / formeln-und-funktionen-einführung-der-vlookup-funktion [Die VLOOKUP-Funktion in Excel] | Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, mit der Werte aus verschiedenen Bereichen und Tabellen gesucht werden. link: / tips-countif-in-microsoft-excel [COUNTIF in Excel 2016] | Zählen Sie Werte mit Bedingungen, die diese erstaunliche Funktion verwenden. Sie müssen Ihre Daten nicht filtern, um einen bestimmten Wert zu zählen.
Die Countif-Funktion ist wichtig, um Ihr Dashboard vorzubereiten.
link: / excel-formel-und-funktion-excel-sumif-funktion [Verwendung der SUMIF-Funktion in Excel] | Dies ist eine weitere wichtige Funktion des Dashboards. Auf diese Weise können Sie Werte unter bestimmten Bedingungen zusammenfassen.