Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema InputBox
BildScreenshot zu InputBox InputBox-Seite mit Beispielarbeitsmappe aufrufen

Berechnete Felder in allen Pivots der Arbeitsmappe abändern

Betrifft: Berechnete Felder in allen Pivots der Arbeitsmappe abändern von: haze
Geschrieben am: 07.02.2020 10:08:43

Guten Morgen zusammen,


eine Frage, zu der ich leider auch keine bisherige Antwort finden konnte: Lassen sich mit einem Befehl berechnete Felder nicht für eine, sondern für alle Pivot Tabellen (mit gleichem Aufbau und Logik) der Arbeitsmappe abändern?


###


Hintergrund: In Power Query suche ich mir alle nötigen Informationen zusammen, aus denen sich ein Gesamtbericht ergibt.


Im nächsten Moment gilt es diesen Gesamtbericht je Abteilung zu splitten und per Mail zu versenden (natürlich via Makro).


Das Problem: Wenn ich die heruntergeladenen Daten der PowerQuery (Tabellenblatt Database) nur nach der entsprechenden Abteilung filtere bringt das leider nichts, denn die Pivot, die darauf zugereift, wertet trotz Filter alle Einträge aus, die da sind. Das ist so nicht gewollt, da eine Abteilung nicht sehen soll, was die andere macht.


Am einfachsten wäre es natürlich nur mit einer Pivot und einer PowerBI Datenquelle zu arbeiten und per Makro für jeden Bereich kurz den Filter in der PowerQuery entsprechend zu setzen, Pivot aktualisieren, Email verschicken und Filter auf nächste Abteilung setzen, usw. usw. - das übersteigt dann doch aber meine bescheidenen Möglichkeiten (und auch dazu habe ich im Netz ebenfalls nichts praktikables gefunden).


Insofern müssen Krücken her:


Krücke 1: Ich filtere nach der PowerQuery im Tabellenblatt "Database" das Datenmaterial nach der jeweiligen Abteilung, kopiere die Daten in ein neues Tabellenblatt (damit die ausgefilterten Einträge für die Pivot nicht mehr erreichbar sind) und lege per Namensmanager den Bereich fest, auf den dann die Pivot zugreift.


- Das Ganze habe ich schon einmal praktiziert: Sehr aufwändig und langsam.


Krücke 2: Ich arbeite in PowerQuery selber und dupliziere meine Gesamtanfrage für jede Abteilung, lege dort als Filter die jeweilige Abteilung in der Abfrage fest und lade die Daten in einzelne Tabellenblätter runter. Dadurch habe ich ein Tabellenblatt mit dem Gesamtreport und 9 weitere für die einzelnen Abteilung. Zugegebenerweise auch nicht schön, aber noch ok.


Nun würde ich je Abteilung ein weiteres Tabellenblatt einfügen, in der die Pivot (analog zu der des Gesamtreports) zu den Abteilungsdaten liegt. Nun zur Frage:


###


Frage: Lassen sich mit einem Befehl bspw. "For each Pivot" die Korrekturen an den berechneten Feldern nicht nur in der hier nachfolgenden PivotTables("CCReport"), sondern in allen Pivots umsetzen? Der Code wird sonst sehr lange, wenn ich das umständlicherweise für jede Pivot einzeln machen muss (dann würde ich fast wieder zu Krücke 1 tendieren)...


An der Stelle sei erwähnt, dass Aufbau und Logik der Pivots alle identisch wären, nur eben die Datenquellen nicht (je Abteilung, aber auch da ändert sich der Aufbau nicht).


Das war jetzt zugegebenerweise ziemlich lang, würde mich trotzdem freuen, wenn sich jemand die Mühe macht!


Besten Dank und viele Grüße

Haze


Code:



Sub Refresh()

Dim Monatseingabe As Integer


Application.ScreenUpdating = False


'## Dateien aktualisieren


Sheets("Database").Select

Sheets("Database").Range("A2").Select '/Datenverbindung Power Query aktualisieren

Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False


For Each Pvts In ActiveWorkbook.PivotCaches '/ konkreter Befehler zur Pivot Aktualisierung

Pvts.Refresh

Next


'## Abfrage, um welchen Monat es sich handelt


Monatseingabe = InputBox("Bitte Monat des Berichts angeben:") '/Abfrage des Berechnungsmonats für weitere Berechnungen


Sheets("Report").Activate

Sheets("Report").Select


Range("C1").Select

ActiveCell.FormulaR1C1 = Monatseingabe & "/1/2020"



If Monatseingabe = "1" Then

ActiveSheet.PivotTables("CCReport").CalculatedFields("act. Month").StandardFormula = "=Januar" 'Act. Month

ActiveSheet.PivotTables("CCReport").CalculatedFields("IST cumulated").StandardFormula = "=Januar" 'YTD cum.

ActiveSheet.PivotTables("CCReport").CalculatedFields("PY cumulated").StandardFormula = "=Invoices.01.01.2019" 'PY YTD cum.

ActiveSheet.PivotTables("CCReport").CalculatedFields("VS Feld").StandardFormula = "=Januar*12" 'Trend



Else



If Monatseingabe = "2" Then

ActiveSheet.PivotTables("CCReport").CalculatedFields("act. Month").StandardFormula = "=Februar" 'Act. Month

ActiveSheet.PivotTables("CCReport").CalculatedFields("IST cumulated").StandardFormula = "=Januar+Februar" 'YTD cum.

ActiveSheet.PivotTables("CCReport").CalculatedFields("PY cumulated").StandardFormula = "=Invoices.01.01.2019+Invoices.01.02.2019" 'PY YTD cum.

ActiveSheet.PivotTables("CCReport").CalculatedFields("VS Feld").StandardFormula = "=((Januar+Februar)/2)*12" 'Trend


Else



End If

....

Betrifft: doppelt! owt
von: Torsten
Geschrieben am: 07.02.2020 10:10:26



Beiträge aus dem Excel-Forum zum Thema "Berechnete Felder in allen Pivots der Arbeitsmappe abändern"