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