Pivot bietet Ihnen optimale Möglichkeiten größere Datenmengen gezielt zu analysieren. Mit minimalem Aufwand können Sie den Datenbereich einer Pivot-Tabelle dynamisieren. Dazu stelle ich Ihnen heute eine kleine Makrolösung vor.
Die beiden Makros werden im VBA-Editor nicht als Modul in Ihrem VBAProject gespeichert, sondern im Ordner “Microsoft Excel Objekte” und dort in der Tabelle in der Ihr Datenbereich für die Pivot-Tabelle liegt.
Die Funktion der beiden Makros ist schnell erklärt. Das Makro “dynPivotbereich” setzt einen Namen für den gewünschten Datenbereich. Im Beispiel hat dieser Bereich 4 Spalten von Spalte A = “C1” bis Spalte D = “C4”. Das “C” steht hier für Column = Spalte. Die Zeile wird mit dem Buchstaben R = Row angegeben. Im Beispiel beginnt der Datenbereich in Zeile 1 = “R1”.
RefersToR1C1:="='Grunddaten'!R1C1:R" & letzteZeile & "C4"
Das Ende des Datenbereichs soll dynamisch gestaltet werden, um die Daten nachträglich erweitern zu können, ohne die Pivot Tabelle ändern oder neu anlegen zu müssen. Hierfür wird die Variable “letzteZeile” verwendet. Dafür wird zunächst die letzte genutzte Zeile in Spalte A = 1 ermittelt.
letzteZeile = Sheets("Grunddaten").Cells(Rows.Count, 1).End(xlUp).Row
Ermitteln Sie die letzte Zeile immer in einer Spalte, in der es keine leeren Zellen geben kann. Alternativ gibt es auch andere Möglichkeiten die letzte genutzte Zeile zu ermitteln. Mehr dazu finden Sie in diesem Beitrag.
Im zweiten Schritt wird nun der Name “Pivotgrundlage” für den genutzten Zellbereich gesetzt.
ActiveWorkbook.Names.Add Name:="Pivotgrundlage", RefersToR1C1:="='Grunddaten'!R1C1:R" & letzteZeile & "C4"
Der Name wird immer dann neu vergeben, wenn Sie das Tabellenblatt mit dem Datenbereich verlassen. Der Start des Makros wird über “Worksheet_Deactivate” aufgerufen. So wird sichergestellt, dass Sie nach Änderungen immer den aktuellen Datenbereich für die Pivot-Tabelle nutzen.
Zusammenfassend können Sie den folgenden Programmcode in Ihrem VBAProject in die Tabelle für den dynamischen Datenbereich kopieren:
Option Explicit
Private Sub Worksheet_Deactivate()
Call dynPivotbereich
End Sub
Sub dynPivotbereich()
'Name: Pivotgrundlage
Dim letzteZeile As Long
letzteZeile = Sheets("Grunddaten").Cells(Rows.Count, 1).End(xlUp).Row
ActiveWorkbook.Names.Add Name:="Pivotgrundlage", RefersToR1C1:="='Grunddaten'!R1C1:R" & letzteZeile & "C4"
End Sub
Eine fertige Musterdatei mit dem Makro können Sie auch direkt herunterladen. Möchten Sie mehr Spalten in Ihre Pivot-Tabelle übernehmen oder den Spaltenbereich dynamisieren können Sie das Makro entsprechend anpassen.
Je nach Einstellung Ihrer Pivot-Tabelle müssen Sie die Pivot-Tabelle jetzt nur noch aktualisieren bevor die Änderungen an der Datenbasis übernommen werden.
Private Sub Worksheet_Activate()
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
End Sub
Hallo,
vielen Dank für diese hilfreiche Funktion. Leider funktioniert es nur, wenn ich die Musterdatei verwende, nicht aber, wenn ich den Programmcode selbst einfüge und anpasse. Ich hab die Codes überprüft, sie sind exakt gleich.
An was kann das liegen? Muss noch irgendwas anderes eingestellt werden?
MfG Markus Loy
@Markus Loy: Eine Ferndiagnose ist da recht schwierig. Grundsätzlich gibt es keine Besonderheiten zu beachten. Eventuell sind in der Arbeitsmappe Makros nicht aktiviert?