PIVOT-Felder per Makro dynamisch abwählen

Question

Hallo liebe Excel-Profis,

ich habe nun gefühlt das halbe Internet nach einer Lösung zu meinem Problem abgesucht. Leider bin ich nicht fündig geworden.

<b>Hier mein Problem:</b>

In meiner PIVOT-Tabelle möchte ich per Makro die Ausgewählten Werte (nicht Berichtsfilter, nicht Spaltenbeschriftung, nicht Zeilenbeschriftung, sondern "Werte") komplett abwählen.

Zeichne ich ein Makro dazu auf, kommt diese relevante Code-Zeile ins Spiel:

"ActiveSheet.PivotTables("PivotTable2").PivotFields("Summe von Jahr 11"). _
Orientation = xlHidden"

Das Problem ist nur, ich weiß vorher nicht welches "Summe von Jahr" ausgewählt ist, um es per Makro dynamisch abwählen zu können. Gibt es eine Möglichkeit alle Feldwerte per Makro abzuwählen?

Vielen Dank für eure Hilfe.

Answer 1

hi all ^^

eher eine anfängerfrage *g*

gruss nighty

ActiveSheet.PivotTables("PivotTable2").PivotFields("Summe von Jahr " & DeineVariable).Orientation = xlHidden

oder wert der activen zelle

ActiveSheet.PivotTables("PivotTable2").PivotFields("Summe von Jahr " & ActiveCell).Orientation = xlHidden

nicht getestet !

gruss nighty

Answer 2

@nighty,

velen Dank für deine Antwort!

Leider kenne ich nicht die ausgewählten "Summe von Jahr X", dass heißt ich suche eine möglichkeit pauschal alle Werte aus der PIVO rauszuhausen, ungeachtet was da momentan ausgewählt ist ... gibt es da einen Weg?

Vielen Dank

Answer 3

Hat keiner eine Idee für mich?

Answer 4

Hallo Marko,

Vielleicht würde es helfen, wenn du mal deine Pivottabelle etwas näher beschreibst. Wie viele Jahre gibt es denn und wie ist die Tabelle formatiert. Stehen die Jahre in Spalten oder Zeilen, gibt es Teilsummen etc.

Auch dein bisheriger Makrocode könnte durchaus helfen, Licht ins Dunkel zu bringen, was genau du eigentlich machen willst.

Gruß Mr. K.

Answer 5

Hallo ExcelKing

die Anzahl der Jahre ist dynamisch und soll vom Makro gesteuert werden (je nach dem, was im Dashboard vorher ausgewählt wurde).

Die Jahre sollen als Spalten angezeigt werden (nicht als Zeilen) und die Werte von "Anzahl" auf "Summe" umgestellt werden. Ich benutze Excel 2010 und nicht die Klassische Ansicht der PIVOT.

Um die neuausgewählten Jahre (im Dashboard) per Makro in der PIVOT auszuwählen, möchte ich pauschal alle vorhanden (alten ausgewählten) Jahre wieder deaktivieren, bzw. aus der Auswahl wieder rausschmeißen, um dann nur die aktuell ausgewählten Jahre darstellen zu lassen.

Die Jahre variieren je nach auswahl, mal kann es 2008, 2009, 2010 und 2011 sein - beim nächsten mal 2010, 2011, 2012 und 2013 sein ...

Ich würde gerne wissen, ob ich die ausgewählten Werte (in der PIVOT Feldliste das Kästchen unten rechts) per Makro komplett zurücksetzen kann, ohne zu wissen, was gerade darinsteht. Pauschal alles deselektieren.

Vielen Dank für die Hilfe und Unterstützung bei dem kniffeligen Thema

Answer 6

Hallo Marko,

ich denke ich hab dich jetzt verstanden. Falls nicht, sag einfach nochmal Bescheid.

Da du immer noch keinen Hinweis auf das Format deiner Quelldaten und die Art der Benutzerauswahl gegeben hast, hier mal mein eigenes Beispiel:

Im Tabellenblatt "Quelldaten" habe ich in Spalte A Bezeichnungen und ab Spalte B bis irgendwas die Jahre. Zeile 1 enthält die Überschriften. Sobald eine oder mehrere beliebige Spalten (oder ein beliebiger Zellbereich dieser Spalten) markiert werden, wird die bereits bestehende Pivottabelle angepasst.
Öffne dazu den VBA Editor (Alt+F11) und füge in das Tabellen-Modul welches deine Quelldaten enthält diesen Code ein. Vergiss nicht Sheets(„Pivottabelle“) und Range(„A1“ bzw. „B1“) an deine Verhältnisse anzupassen.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Set Pivot = Sheets("Pivottabelle") '"Pivottabelle" = Tabellenblatt das die Pivottabelle enthält

Set daten = Target.Parent 'Tabellenblatt das die Daten enthält wird automatisch gespeichert

'alternativ
'Set daten = Sheets("Quelldaten") '"Quelldaten" = Tabellenblatt das die Daten enthält

Set Beginn = daten.Range("A1") '"A1" = linke obere Zelle des Datenbereichs (Überschrift für Bezeichnung)

Set ErstesJahr = daten.Range("B1") '"B1" = Zelle die das erste Jahr enthält.

'Rechte untere Zelle des Datenbereichs wird automatisch ermittelt:
Set Ende = Beginn.CurrentRegion.Cells(ActiveCell.CurrentRegion.Cells.Count)
'Hinweis: Der Datenbereich darf keine kompletten Leerzeilen enthalten.

'Falls der Datenbereich auch Leerzeilen enthalten kann, dann wie folgt:
Application.EnableEvents = False
Set Ende = Cells(Beginn.SpecialCells(xlLastCell).Row, Beginn.End(xlToRight).Column)
Application.EnableEvents = True

Quelle = daten.Parent.Name & "!" & daten.Name & "!" & Beginn.Address & ":" & Ende.Address 'setzt den Bezug zu den Quelldaten zusammen

Pivot.PivotTables(1).SourceData = Quelle 'weist den Quellbezug der Pivottabelle zu
Pivot.PivotTables(1).RefreshTable 'aktualisiert die Pivottabelle

Set pvt = Pivot.PivotTables(1) 'verweist auf die Pivottabelle
pvt.RefreshTable 'aktualisiert die Pivottabelle

For Each pvf In pvt.DataFields 'durchläuft alle Wertefelder der Pivottabelle
pvf.Orientation = xlHidden '& entfernt diese
Next pvf

For Each c In Target.Columns 'Durchläuft alle markierten Jahre
Feldname = Cells(1, Columns(Columns(c.Column).Address).Column).Value 'Aktuelles Jahr
If Columns(Columns(c.Column).Address).Column >= ErstesJahr.Column And Feldname <> "" Then 'schließt die Bezeichnungsspalte aus.
Set fld = pvt.AddDataField(pvt.PivotFields(Feldname)) 'fügt Jahr als Wertefeld hinzu
fld.Caption = "Summe von " & Feldname 'benennt es um
fld.Function = xlSum 'mach aus Anzahl von eine Summe von
fld.NumberFormat = "#,##0.00" 'formatiert nach Zahlenformat
End If
Next c

End Sub

Answer 7

Ersetze noch Feldname = Cells(1, durch Feldname = Cells(Beginn.Row,
Mr. K.