Wie dynamisch aktualisieren Alle Pivot-Tabellen-Datenquelle in Excel
In einem früheren Artikel haben wir erfahren, wie Sie einzelne Pivot-Tabellen dynamisch ändern und aktualisieren können, indem Sie Datenquellen verkleinern oder erweitern.
In diesem Artikel erfahren Sie, wie Sie alle Pivot-Tabellen in einer Arbeitsmappe dazu bringen können, die Datenquelle automatisch zu ändern. Mit anderen Worten, anstatt jeweils eine Pivot-Tabelle zu ändern, werden wir versuchen, die Datenquelle aller Pivot-Tabellen in der Arbeitsmappe so zu ändern, dass neue Zeilen und Spalten, die den Quelltabellen hinzugefügt wurden, dynamisch eingeschlossen werden und die Änderung der Pivot-Tabellen sofort wiedergegeben wird.
Code in Quelldatenblatt schreiben Da dies vollständig automatisch erfolgen soll, verwenden wir Blattmodule, um Code anstelle eines Kernmoduls zu schreiben. Auf diese Weise können wir „link: / events-in-vba-the-worksheet-events-in-excel-vba [Arbeitsblattereignisse]“ verwenden.
Wenn sich die Quelldaten und Pivot-Tabellen in verschiedenen Blättern befinden, schreiben wir den VBA-Code, um die Datenquelle der Pivot-Tabelle in das Blattobjekt zu ändern, das die Quelldaten enthält (nicht das, das Pivot-Tabellen enthält). Drücken Sie STRG + F11, um den VB-Editor zu öffnen. Gehen Sie nun zum Projektexplorer und suchen Sie das Blatt, das die Quelldaten enthält. Doppelklicken Sie darauf.
Ein neuer Codierungsbereich wird geöffnet. Möglicherweise sehen Sie keine Änderung, aber jetzt haben Sie Zugriff auf die Arbeitsblattereignisse.
Klicken Sie auf das linke Dropdown-Menü und wählen Sie das Arbeitsblatt aus. Wählen Sie im linken Dropdown-Menü die Option Deaktivieren. Auf dem Codebereichsnamen worksheet_deativate wird ein leeres Sub angezeigt. Unser Code zum dynamischen Ändern von Quelldaten und zum Aktualisieren der Pivot-Tabelle wird in diesen Codeblock aufgenommen. Dieser Code wird immer dann ausgeführt, wenn Sie vom Datenblatt zu einem anderen Blatt wechseln. Sie können über alle Arbeitsblattereignisse link: / events-in-vba-das-Arbeitsblatt-Ereignisse-in-excel-vba [hier] lesen.
Jetzt können wir den Code implementieren.
Quellcode zum dynamischen Aktualisieren aller Pivot-Tabellen in der Arbeitsmappe mit neuem Bereich Um zu erklären, wie es funktioniert, habe ich eine Arbeitsmappe. Diese Arbeitsmappe enthält drei Blätter. Sheet1 enthält die Quelldaten, die sich ändern können. Sheet2 und Sheet3 enthalten Pivot-Tabellen, die von den Quelldaten von Sheet2 abhängen.
Jetzt habe ich diesen Code in den Codierungsbereich von sheet1 geschrieben. Ich verwende das Ereignis Worksheet_Deactivate, sodass dieser Code ausgeführt wird, um die Pivot-Tabelle zu aktualisieren, wenn wir vom Quelldatenblatt wechseln.
Private Sub Worksheet_Deactivate() Dim source_data As Range 'Determining last row and column number lstrow = Cells(Rows.Count, 1).End(xlUp).Row lstcol = Cells(1, Columns.Count).End(xlToLeft).Column 'Setting the new range Set source_data = Range(Cells(1, 1), Cells(lstrow, lstcol)) 'Code to loop through each sheet and pivot table For Each ws In ThisWorkbook.Worksheets For Each pt In ws.PivotTables pt.ChangePivotCache _ ThisWorkbook.PivotCaches.Create( _ SourceType:=xlDatabase, _ SourceData:=source_data) Next pt Next ws End Sub
Wenn Sie eine ähnliche Arbeitsmappe haben, können Sie diese Daten direkt kopieren. Ich habe erklärt, dass dieser Code unten funktioniert, damit Sie ihn gemäß Ihren Anforderungen ändern können.
Sie können die Wirkung dieses Codes in GIF unten sehen.
Wie ändert dieser Code automatisch die Quelldaten und aktualisiert die Pivot-Tabellen? Zuerst haben wir ein worksheet_deactivate-Ereignis verwendet. Dieses Ereignis wird nur ausgelöst, wenn das Blatt mit dem Code umgeschaltet oder deaktiviert wird. So läuft der Code also automatisch ab.
Um die gesamte Tabelle dynamisch als Datenbereich abzurufen, bestimmen wir die letzte Zeile und die letzte Spalte.
lstrow = Cells (Rows.Count, 1) .End (xlUp) .Row
lstcol = Cells (1, Columns.Count) .End (xlToLeft) .Column
Mit diesen beiden Zahlen definieren wir die Quelldaten. Wir sind sicher, dass der Quelldatenbereich immer bei A1 beginnt. Sie können Ihre eigene Anfangszellenreferenz definieren.
Set source_data = Range (Zellen (1, 1), Zellen (lstrow, lstcol))
Jetzt haben wir die Quelldaten, die dynamisch sind. Wir müssen es nur in der Pivot-Tabelle verwenden.
Da wir nicht wissen, wie viele Pivot-Tabellen eine Arbeitsmappe gleichzeitig enthalten wird, werden wir jedes Blatt und die Pivot-Tabellen jedes Blattes durchlaufen. Damit bleibt kein Pivot-Tisch übrig. Dafür verwenden wir verschachtelte for-Schleifen.
Für jedes ws in ThisWorkbook.Worksheets
Für jeden Punkt in ws.PivotTables
pt.ChangePivotCache _
ThisWorkbook.PivotCaches.Create (_
SourceType: = xlDatabase, _
SourceData: = source_data)
Nächster Punkt
Weiter ws
Die erste Schleife durchläuft jedes Blatt. Die zweite Schleife durchläuft jede Pivot-Tabelle in einem Blatt. Die Pivot-Tabellen sind der Variablen pt zugeordnet. * Wir verwenden die ChangePivotCache-Methode des pt-Objekts. Wir erstellen dynamisch einen Pivot-Cache mit der ThisWorkbook.PivotCaches.Create-Methode. Diese Methode verwendet zwei Variablen SourceType und SourceData. Als Quelltyp deklarieren wir xlDatabase und als SourceData übergeben wir den zuvor berechneten Bereich source_data.
Und das ist alles. Wir haben unsere Pivot-Tabellen automatisiert. Dadurch werden automatisch alle Pivot-Tabellen in der Arbeitsmappe aktualisiert.
Auf diese Weise können Sie die Datenquellenbereiche aller Pivot-Tabellen in einer Arbeitsmappe in Excel dynamisch ändern. Ich hoffe ich war erklärend genug. Wenn Sie Fragen zu diesem Artikel haben, lassen Sie es mich im Kommentarbereich unten wissen.
Verwandte Artikel:
Wie Pivot-Tabelle Datenquellenbereich in Excel dynamisch aktualisieren: Zum dynamischen Ändern der Quelldatenbereich von Pivot-Tabellen verwenden wir Pivot-Caches. Diese wenigen Zeilen können jede Pivot-Tabelle dynamisch aktualisieren, indem sie den Quelldatenbereich ändern.
link: / custom-functions-in-vba-How-to-Auto-Refresh-Pivot-Tabellen-using-vba-excel [So aktualisieren Sie Pivot-Tabellen automatisch mit VBA]: Zum automatischen Aktualisieren Ihrer Pivot-Tabellen, die Sie verwenden können VBA-Ereignisse. Verwenden Sie diese einfache Codezeile, um Ihre Pivot-Tabelle automatisch zu aktualisieren. Sie können eine der drei Methoden zum automatischen Aktualisieren von Pivot-Tabellen verwenden.
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 an Excel arbeiten 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.