In einem früheren Artikel haben wir erfahren, wie Sie einzelne Pivot-Tabellen dynamisch ändern und aktualisieren können, wenn Datenquellen verkleinert oder erweitert werden.
In diesem Artikel erfahren Sie, wie Sie bei allen Pivot-Tabellen in einer Arbeitsmappe automatisch die Datenquelle ändern können. Mit anderen Worten, anstatt eine Pivot-Tabelle nach der anderen zu ändern, versuchen wir, die Datenquelle aller Pivot-Tabellen in der Arbeitsmappe zu ändern, um neue Zeilen und Spalten, die zu Quelltabellen hinzugefügt wurden, dynamisch einzuschließen und die Änderung in den Pivot-Tabellen sofort widerzuspiegeln.
Code in Quelldatenblatt schreiben
Da dies vollständig automatisch erfolgen soll, verwenden wir anstelle eines Kernmoduls Blattmodule, um Code zu schreiben. Dadurch können wir Arbeitsblattereignisse verwenden.
Wenn sich die Quelldaten und die Pivot-Tabellen in verschiedenen Blättern befinden, schreiben wir den VBA-Code, um die Pivot-Tabellen-Datenquelle im Blattobjekt zu ändern, das die Quelldaten enthält (nicht das Pivot-Tabellen enthält).
Drücken Sie STRG+F11, um den VB-Editor zu öffnen. Gehen Sie nun zum Projekt-Explorer und suchen Sie das Blatt, das die Quelldaten enthält. Doppelklicken Sie darauf.
Es öffnet sich ein neuer Codierbereich. 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ü Deaktivieren aus. Sie sehen ein leeres Sub, das auf den Codebereichsnamen worksheet_deativate geschrieben wird. 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. Alle Arbeitsblatt-Ereignisse können Sie hier nachlesen.
Jetzt sind wir bereit, den Code zu 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. Dieses Arbeitsbuch 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 Codierbereich von Sheet1 geschrieben. Ich verwende das Worksheet_Deactivate-Ereignis, damit 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 'Bestimmen der letzten Zeilen- und Spaltennummer lstrow = Cells(Rows.Count, 1).End(xlUp).Row lstcol = Cells(1, Columns.Count).End(xlToLeft).Column 'Einstellen des neuen Bereichs Set source_data = Range(Cells(1, 1), Cells(lstrow, lstcol)) 'Code zum Durchlaufen jedes Blatts und jeder Pivot-Tabelle 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 über eine ähnliche Arbeitsmappe verfügen, können Sie diese Daten direkt kopieren. Ich habe die Funktionsweise dieses Codes unten erklärt, damit Sie ihn nach Ihren Bedürfnissen ändern können.
Sie können die Wirkung dieses Codes in der folgenden GIF-Datei sehen.
Wie ändert dieser Code automatisch Quelldaten und aktualisiert Pivot-Tabellen?
Als erstes haben wir ein worksheet_deactivate Event verwendet. Dieses Ereignis wird nur ausgelöst, wenn das Blatt mit dem Code umgeschaltet oder deaktiviert wird. So wird der Code automatisch ausgeführt.
Um dynamisch die gesamte Tabelle als Datenbereich zu erhalten, bestimmen wir die letzte Zeile und 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 source_data. Wir sind uns sicher, dass der Quelldatenbereich immer bei A1 beginnt. Sie können Ihren eigenen Zellbezug für den Anfang definieren.
Set source_data = Range(Cells(1, 1), Cells(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, durchlaufen wir jedes Blatt und die Pivot-Tabellen jedes Blatts. Damit keine Pivot-Tabelle übrig bleibt. Dazu verwenden wir verschachtelte for-Schleifen.
Für jedes ws in ThisWorkbook.Worksheets
Für jeden Punkt in ws.PivotTables
pt.ChangePivotCache _
ThisWorkbook.PivotCaches.Create( _
Quelltyp:=xlDatenbank, _
SourceData:=source_data)
Nächster Punkt
Nächstes ws
Die erste Schleife durchläuft jedes Blatt. Die zweite Schleife durchläuft jede Pivot-Tabelle in einem Blatt.
Die Pivot-Tabellen sind Variablen zugeordnet Pkt. Wir verwenden die ChangePivotCache-Methode des pt-Objekts. Wir erstellen dynamisch einen Pivot-Cache mit ThisWorkbook.PivotCaches.Create
Methode. Diese Methode benötigt zwei Variablen SourceType und SourceData. Als Quelltyp deklarieren wir xlDatabase und als SourceData übergeben wir den zuvor berechneten source_data-Bereich.
Und das ist alles. Wir haben unsere Pivot-Tabellen automatisiert. Dadurch werden automatisch alle Pivot-Tabellen in der Arbeitsmappe aktualisiert.
Also, Leute, so 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.
So aktualisieren Sie den Datenquellenbereich der Pivot-Tabelle in Excel dynamisch: Um den Quelldatenbereich von Pivot-Tabellen dynamisch zu ändern, verwenden wir Pivot-Caches. Diese wenigen Zeilen können jede Pivot-Tabelle dynamisch aktualisieren, indem der Quelldatenbereich geändert wird.
So aktualisieren Sie Pivot-Tabellen mit VBA automatisch: Um Ihre Pivot-Tabellen automatisch zu aktualisieren, können Sie VBA-Ereignisse verwenden. Verwenden Sie diese einfache Codezeile, um Ihre Pivot-Tabelle automatisch zu aktualisieren. Sie können eine von drei Methoden zum automatischen Aktualisieren von Pivot-Tabellen verwenden.
Makro ausführen, wenn eine Änderung am Blatt im angegebenen Bereich vorgenommen wurde: In Ihren VBA-Praktiken müssen Sie Makros ausführen, wenn sich ein bestimmter Bereich oder eine Zelle ändert. In diesem Fall verwenden wir zum Ausführen von Makros bei einer Änderung eines Zielbereichs das Änderungsereignis.
Makro ausführen, wenn Änderungen am Blatt vorgenommen werden | Um Ihr Makro bei jeder Blattaktualisierung auszuführen, verwenden wir die Arbeitsblattereignisse von VBA.
Einfachster VBA-Code zum Hervorheben der aktuellen Zeile und Spalte mit | Verwenden Sie dieses kleine VBA-Snippet, um die aktuelle Zeile und Spalte des Blatts hervorzuheben.
Die Arbeitsblattereignisse in Excel VBA | Das Arbeitsblattereignis ist wirklich nützlich, wenn Sie möchten, dass Ihre Makros ausgeführt werden, wenn ein bestimmtes Ereignis auf dem Blatt auftritt.
Populäre Artikel:
50 Excel-Kurzbefehle zur Steigerung Ihrer Produktivität | Werden Sie schneller bei Ihrer Aufgabe. Mit diesen 50 Tastenkombinationen arbeiten Sie noch schneller mit Excel.Die SVERWEIS-Funktion in Excel | Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, die verwendet wird, um Werte aus verschiedenen Bereichen und Blättern zu suchen.
ZÄHLENWENN in Excel 2016 | Zählen Sie Werte mit Bedingungen mit dieser erstaunlichen Funktion. Sie müssen Ihre Daten nicht filtern, um einen bestimmten Wert zu zählen. Die Countif-Funktion ist unerlässlich, um Ihr Dashboard vorzubereiten.
So verwenden Sie die SUMIF-Funktion in Excel | Dies ist eine weitere wesentliche Funktion des Dashboards. Dies hilft Ihnen, Werte unter bestimmten Bedingungen zusammenzufassen.