Dynamischer Datenbereich für Pivot-Tabellen

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.

VBA Project

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

Download Musterdatei

Musterdatei: Dynamischer Datenbereich für Pivot-Tabellendynamischer_datenbereich.xlsm

 
Gefällt Ihnen der Beitrag?

2 Gedanken zu „Dynamischer Datenbereich für Pivot-Tabellen“

  1. 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

    Antworten

Schreibe einen Kommentar

Diese Website verwendet Akismet, um Spam zu reduzieren. Erfahre mehr darüber, wie deine Kommentardaten verarbeitet werden.