VBA Pivot Table - Schritte zum Erstellen einer Pivot-Tabelle in VBA

Excel VBA Pivot-Tabelle

Pivot-Tabellen sind das Herzstück der Zusammenfassung des Berichts über eine große Datenmenge. Wir können auch den Prozess der Erstellung einer Pivot-Tabelle durch VBA-Codierung automatisieren. Sie sind ein wichtiger Bestandteil jedes Berichts oder Dashboards. In Excel ist es einfach, Tabellen mit einer Schaltfläche zu erstellen. In VBA müssen wir jedoch einige Codes schreiben, um unsere Pivot-Tabelle zu automatisieren, bevor Excel 2007 und seine älteren Versionen in VBA nicht benötigt wurden Erstellen Sie einen Cache für Pivot-Tabellen, aber in Excel 2010 und seinen neueren Versionen sind Caches erforderlich.

VBA kann uns an unserem Arbeitsplatz jede Menge Zeit sparen. Obwohl das Beherrschen nicht so einfach ist, lohnt es sich, Zeit zu investieren, um dies zu lernen. Ich habe 6 Monate gebraucht, um den Prozess des Erstellens von Pivot-Tabellen über VBA zu verstehen. Sie wissen, was diese 6 Monate für mich Wunder bewirkt haben, weil ich beim Versuch, die Pivot-Tabelle zu erstellen, so viele Fehler gemacht habe.

Tatsächlich habe ich aus meinen Fehlern gelernt, und jetzt schreibe ich diesen Artikel, um Ihnen zu zeigen, wie Sie Pivot-Tabellen mit Code erstellen.

Mit nur einem Klick auf eine Schaltfläche können wir Berichte erstellen.

Schritte zum Erstellen einer Pivot-Tabelle in VBA

Um eine Pivot-Tabelle zu erstellen, ist es wichtig, Daten zu haben. Dafür habe ich einige Dummy-Daten erstellt. Sie können die Arbeitsmappe herunterladen, um mit denselben Daten mit mir zu folgen.

Schritt 1: Pivot-Tabelle ist ein Objekt, das auf die Pivot-Tabelle verweist und die Variable als PivotTables deklariert.

Code:

Sub PivotTable () Dim PTable As PivotTable End Sub

Schritt 2: Bevor wir zuerst eine Pivot-Tabelle erstellen, müssen wir einen Pivot-Cache erstellen, um die Datenquelle zu definieren.

In den regulären Arbeitsblättern erstellt die Pivot-Tabelle, ohne uns zu stören, einen Pivot-Cache im Hintergrund. Aber in VBA müssen wir erstellen.

Definieren Sie dazu die Variable einen PivotCache.

Code:

PCache als PivotCache dimmen

Schritt 3: Um den Pivot-Datenbereich zu bestimmen, definieren Sie die Variable als Bereich.

Code:

PRange As Range dimmen

Schritt 4: Um eine Pivot-Tabelle einzufügen, benötigen wir ein separates Blatt, um Arbeitsblätter für die Pivot-Tabelle hinzuzufügen und die Variable als Arbeitsblatt zu deklarieren.

Code:

PSheet als Arbeitsblatt dimmen

Schritt 5: Um auf die Daten zu verweisen, die das Arbeitsblatt enthalten, deklarieren Sie eine weitere Variable als Arbeitsblatt.

Code:

DSheet als Arbeitsblatt dimmen

Schritt 6: Um die zuletzt verwendete Zeile und Spalte zu finden, definieren Sie zwei weitere Variablen als Long.

Code:

Dim LR So lange Dim LC As Long

Schritt 7: Jetzt müssen wir ein neues Blatt einfügen, um eine Pivot-Tabelle zu erstellen. Wenn zuvor ein Pivot Sheet vorhanden ist, müssen wir dieses löschen.

Schritt 8: Setzen Sie nun die Objektvariablen PSheet und DSheet auf Pivot Sheet bzw. Data Sheet.

Schritt 9: Suchen Sie die zuletzt verwendete Zeile und die zuletzt verwendete Spalte im Datenblatt.

Schritt 10: Stellen Sie nun den Drehbereich mithilfe der letzten Zeile und der letzten Spalte ein.

Dadurch wird der Datenbereich perfekt eingestellt. Der Datenbereich wird automatisch ausgewählt, auch wenn Daten im Datenblatt hinzugefügt oder gelöscht werden.

Schritt 11: Bevor wir eine Pivot-Tabelle erstellen, müssen wir einen Pivot-Cache erstellen. Legen Sie die Pivot-Cache-Variable mithilfe des folgenden VBA-Codes fest.

Schritt 12: Erstellen Sie nun eine leere Pivot-Tabelle.

Schritt 13: Nach dem Einfügen der Pivot-Tabelle müssen wir zuerst das Zeilenfeld einfügen. Also werde ich das Zeilenfeld als meine Länderspalte einfügen.

Hinweis: Laden Sie die Arbeitsmappe herunter, um die Datenspalten zu verstehen.

Schritt 14: Nun ein weiteres Element, das ich als zweites Positionselement in das Zeilenfeld einfügen werde. Ich werde das Produkt als zweite Werbebuchung in das Zeilenfeld einfügen.

Schritt 15: Nach dem Einfügen der Spalten in das Zeilenfeld müssen Werte in das Spaltenfeld eingefügt werden. Ich werde das "Segment" in das Spaltenfeld einfügen.

Schritt 16: Jetzt müssen wir Zahlen in das Datenfeld einfügen. Fügen Sie also "Sales" in das Datenfeld ein.

Schritt 17: Wir sind mit dem Pivot-Tabellenzusammenfassungsteil fertig. Jetzt müssen wir die Tabelle formatieren. Verwenden Sie den folgenden Code, um die Pivot-Tabelle zu formatieren.

Hinweis: Um mehr verschiedene Tabellenstile zu erhalten, zeichnen Sie diese Makros auf und rufen Sie die Tabellenstile ab.

Fügen Sie den folgenden Code unten hinzu, um die Elemente der Zeilen-Zeilenwerte in Tabellenform anzuzeigen.

Ok, wir sind fertig, wenn wir diesen Code mit der Taste F5 oder manuell ausführen, sollten wir die Pivot-Tabelle wie folgt erhalten.

Auf diese Weise können wir mithilfe der VBA-Codierung den Prozess der Erstellung einer Pivot-Tabelle automatisieren.

Als Referenz habe ich den folgenden Code angegeben.

Sub PivotTable() Dim PTable As PivotTable Dim PCache As PivotCache Dim PRange As Range Dim PSheet As Worksheet Dim DSheet As Worksheet Dim LR As Long Dim LC As Long On Error Resume Next Application.DisplayAlerts = False Application.ScreenUpdating = False Worksheets("Pivot Sheet").Delete 'This will delete the exisiting pivot table worksheet Worksheets.Add After:=ActiveSheet ' This will add new worksheet ActiveSheet.Name = "Pivot Sheet" ' This will rename the worksheet as "Pivot Sheet" On Error GoTo 0 Set PSheet = Worksheets("Pivot Sheet") Set DSheet = Worksheets("Data Sheet") 'Find Last used row and column in data sheet LR = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LC = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column 'Set the pivot table data range Set PRange = DSheet.Cells(1, 1).Resize(LR, LC) 'Set pivot cahe Set PCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=PRange) 'Create blank pivot table Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), TableName:="Sales_Report") 'Insert country to Row Filed With PSheet.PivotTables("Sales_Report").PivotFields("Country") .Orientation = xlRowField .Position = 1 End With 'Insert Product to Row Filed & position 2 With PSheet.PivotTables("Sales_Report").PivotFields("Product") .Orientation = xlRowField .Position = 2 End With 'Insert Segment to Column Filed & position 1 With PSheet.PivotTables("Sales_Report").PivotFields("Segment") .Orientation = xlColumnField .Position = 1 End With 'Insert Sales column to the data field With PSheet.PivotTables("Sales_Report").PivotFields("Sales") .Orientation = xlDataField .Position = 1 End With 'Format Pivot Table PSheet.PivotTables("Sales_Report").ShowTableStyleRowStripes = True PSheet.PivotTables("Sales_Report").TableStyle2 = "PivotStyleMedium14" 'Show in Tabular form PSheet.PivotTables("Sales_Report").RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub