Microsoft Excel

Herbers Excel/VBA-Archiv

Aktualisierung von Pivot Tabellen

Betrifft: Aktualisierung von Pivot Tabellen von: Chris
Geschrieben am: 09.10.2020 16:03:46

Hi Team,

ich hoffe ihr könnt mir helfen.
Folgende Situation:
Ich habe mehrere Worksheets mit Daten vorliegen
In einem umfangreichen Macro habe ich Formeln hinterlegt, das die Daten in den Worksheets verarbeitet.
Es sind vorwiegende vlookups
Bei einigen dieser Vlookups muss ich eine Hilfsspalte erstellen, bestimmte Werte auslesen lassen und je nach ausgelesenem Wert ganze Reihen löschen lassen. Nach dem Löschen der Reihen wird die Hilfsspalte gelöscht.
Das alles funktioniert tadellos...doch dann kommt das Problem, das die Pivottabellen sich nicht automatisch aktualisieren und wenn ich es manuell vornehme die Formatierung der Pivot verloren geht, weil die Datenquelle sich verändert.
Wenn ich im Pivot tool mit click auf "Change Data Source" clicke sehe ich das die Range der Datenquelle nicht die gesamte Tabelle erfasst.
Dabei habe ich versucht das zu verhindern in den ich folgenden Code versuchte:

'Set Pivot Table & Source Worksheet
Set Data_Sheet = ThisWorkbook.Worksheets("My_Data_Source")
Set Pivot_Sheet = ThisWorkbook.Worksheets("My_Data_destination")

'Enter in Pivot Table Name
PivotName = "The_name_of_the_Pivot_table"
'Defining Staring Point & Dynamic Range
Data_Sheet.Activate
Set StartPoint = Data_Sheet.Range("A1")
LastCol = StartPoint.End(xlToRight).Column
DownCell = StartPoint.End(xlDown).Row
Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol))
NewRange = Data_Sheet.Name & "!" & DataRange.Address(ReferenceStyle:=xlR1C1)

'Change Pivot Table Data Source Range Address
Pivot_Sheet.PivotTables(PivotName). _
ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange)

'Ensure Pivot Table is Refreshed
Pivot_Sheet.PivotTables(PivotName).RefreshTable

Aber es scheint keinerlei Effekt zu haben.

Für eure Hilfe wäre ich sehr dankbar.

Chris

Betrifft: AW: Aktualisierung von Pivot Tabellen
von: fcs
Geschrieben am: 14.10.2020 18:13:43

Hallo Chris,

dein Makro ermittelt die letzte ausgefüllte Zeile in Spalte A falsch, wenn dort auch leere Zellen vorhanden sind.

Probiere mal diese Variante um DownCell zu berechnen:
DownCell = Data_Sheet.Cells(Rows.Count, StartPoint.Column).End(xlUp).Row
Die Formatierung des Pivotberichts bekommt man nur schwierig in Griff.
a) für die Zahlenformate im Datenbereich die entsprechende Einstellung und den Wertfeld-Einstellungen machen.
b) Man kann dann noch die Tabellenformatvorlagen nutzen, um Rahmen und Farben für bestimmte Bereiche des Pivotberichts festzulegen.

Andere Formatierungen müssten nach jeder Aktualisierung per Makro festgelegt werden.

LG
Franz

LG
Franz

Betrifft: AW: Aktualisierung von Pivot Tabellen
von: Chris
Geschrieben am: 15.10.2020 13:40:34

Hi Franz,

danke dir erstmal für deine Mühen und Ausführungen. Leider hatte die Änderung von
DownCell = StartPoint.End(xlDown).Row
zu
DownCell = Data_Sheet.Cells(Rows.Count, StartPoint.Column).End(xlUp).Row
keine Veränderung am Verhalten der Pivottabellen. Ich muss leider gestehen, das ich nicht ganz durchschaue, wie der VBA code gestrickt sein muss, um dem System zu sagen, es solle die neue Range der Quelldaten prüfen und dann den neuen Pivotcache an die zu aktualisierende Pivottabelle weiter zu geben.

Momentan muss ich halt nach jeder Aktualisierung der Pivots die Range neu festlegen und die Filter etc. neu setzen.

Solltest du/ihr noch weitere Ideen haben wäre ich super dankbar.

Cheers

Chris