Microsoft Excel

Herbers Excel/VBA-Archiv

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

Pivot Tabellen_Feld_Intems

Betrifft: Pivot Tabellen_Feld_Intems von: lee
Geschrieben am: 07.11.2014 12:58:11

Hallo zusammen,

ich habe eine Excel-Datei mit mehreren Tabellenblättern und in jedem Tabellenblatt gibt mehrere PivotTabellen. Alle PivotTabellen
liegen untereinander. Einige Pivottabelle haben das PivotFeld „WK“ und andere nicht, WK steht für Jahr&Kalenderwoche (z.B. 201431)

Ich möchte über VBA die PivotTabellen mit dem Feld „WK“ ansprechen und nur die letzten 13 Kalenderwochen (Items) einblenden, d.h. wenn heute KW45 ist,
dann müssen KW32 bis KW44 eingeblendet werden und alle KW, welche kleiner gleich 31 sind, müssen ausgeblendet werden.

Im Vorfeld habe ich bereits alle KW <=31 ausgeblendet. Jetzt muss ich wöchentlich die letzten 13 Wochen einblenden.

Versuche mit dem Code:

Sub Pivot ()

Dim ws As Worksheet
Dim pt As PivotTable


intKW = 201445

For Each ws In ActiveWorkbook.Worksheets
  For Each pt In ws.PivotTables
    If pt.PivotFields("WK") <> "" Then ‘Hier muss doch das Makro in die  
                                        andere PivotTabelle springen, wenn in der
                                        ersten PivoT das Feld “WK” nicht auftaucht,
                                        aber esgeht nicht in die nächste PivotT,
                                        sondern es läuft weiter mit dem Code 
          With pt.PivotFields("WK")
            .AutoSort xlAscending, "WK"
            .PivotItems(intKW - 1).Visible = True ‘ die neuen Wochen sollen 
                                                    eingeblendet werden
            .PivotItems(intKW - 2).Visible = True  ‘ die neuen Wochen sollen 
                                                     eingeblendet werden
            .PivotItems(intKW - 3).Visible = True  ‘ die neuen Wochen sollen 
                                                     eingeblendet werden
            .PivotItems(intKW - 14).Visible = False  ‘ die neuen Wochen sollen 
                                                     eingeblendet werden
          End With
    End If
  Next 'pt
Next 'ws

End Sub


Vielleicht habt ihr eine andere Idee!?
Danke im Voraus für die Hilfe.

Gruß
lee

  

Betrifft: AW: Pivot Tabellen_Feld_Intems von: fcs
Geschrieben am: 08.11.2014 16:14:49

Hallo Lee,

hier 2 Makrovarianten, die dies leisten können.

Variante 1 folgt deinem Versuch, die Visible-Eigenschaft der einzelnen Items anzupassen.

Variante 2 setzt den Filter des Feldes zurück (dabei werden alle Items eingeblendet) und setzt dann einen Beschriftungsfilter.

Variante 2 ist eigentlich die elegantere Methode. Wenn du mehrere Pivots auf einem Blatt plaziert hast, dann gibt es aber evtl. das Problem, dass sich Pivots überlagern können wenn alle Daten eingeblendet werden. Dies führt dann zu einem Fehlerabbruch. In diesem Fall muss du dann dei Glück mit Variante 1 versuchen.

Gruß
Franz

Sub Pivot_Var1()

    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pvField As PivotField, pvItem As PivotItem
    Dim intKW
    On Error GoTo Fehler
    intKW = Application.InputBox("Letzte KW im Pivotbericht?", "Pivottabellen aktualisieren", _
            Year(Date - 7) * 100 + Application.WorksheetFunction.WeekNum(Date - 7, 21), _
            Type:=1)
    If intKW = False Then Exit Sub
    For Each ws In ActiveWorkbook.Worksheets
      For Each pt In ws.PivotTables
        Set pvField = pt.PivotFields("WK")
        With pvField
            'prüfen,ob Feld im Bericht plaziert ist
            If .Orientation <> xlHidden Then
            'Prüfen, ob Feld im Zeilen- oder Spaltenbereich
            If .Orientation = xlRowField Or .Orientation = xlColumnField Then
                .AutoSort xlAscending, "WK"
                'PivotItems ein-/ausblenden
                For Each pvItem In pvField.PivotItems
                    With pvItem
                        If Val(.Name) >= intKW - 12 And Val(.Name) <= intKW Then
                            If .Visible = False Then .Visible = True
                        Else
                            If .Visible = True Then .Visible = False
                        End If
                    End With
                Next
            Else
                MsgBox "Das Feld """ & pvField.Name _
                   & """ muss als Zeilen oder Spaltenfeld angeordnet sein!"
            End If
            End If
        End With
Next_pt:
      Next 'pt
    Next 'ws
Fehler:
    With Err
        Select Case .Number
            Case 0 'alles OK
            Case 1004 'Feld "WK" ist in Feldliste der Pivottabelle nicht vorhanden
                Resume Next_pt
            Case Else
                MsgBox "Fehler-Nr.: " & .Number & vbLf & .Description
        End Select
    End With
End Sub


Sub Pivot_Var2()

    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pvField As PivotField
    Dim intKW
    On Error GoTo Fehler
    intKW = Application.InputBox("Letzte KW im Pivotbericht?", "Pivottabellen aktualisieren", _
            Year(Date - 7) * 100 + Application.WorksheetFunction.WeekNum(Date - 7, 21), _
            Type:=1)
    If intKW = False Then Exit Sub
    For Each ws In ActiveWorkbook.Worksheets
      For Each pt In ws.PivotTables
        Set pvField = pt.PivotFields("WK")
        With pvField
            'prüfen,ob Feld im Bericht plaziert ist
            If .Orientation <> xlHidden Then
            'Prüfen, ob Feld im Zeilen- oder Spaltenbereich
            If .Orientation = xlRowField Or .Orientation = xlColumnField Then
                .AutoSort xlAscending, "WK"
                'Beschriftungsfilter zurücksetzen
                .ClearLabelFilters
                'Beschriftungsfilter setzen
                .PivotFilters.Add Type:=xlCaptionIsBetween, _
                    Value1:=intKW - 12, _
                    Value2:=intKW
            Else
                MsgBox "Das Feld """ & pvField.Name _
                  & """ muss als Zeilen oder Spaltenfeld angeordnet sein!"
            End If
            End If
        End With
Next_pt:
      Next 'pt
    Next 'ws
Fehler:
    With Err
        Select Case .Number
            Case 0 'alles OK
            Case 1004 'Feld "WK" ist in Feldliste der Pivottabelle nicht vorhanden
                Resume Next_pt
            Case Else
                MsgBox "Fehler-Nr.: " & .Number & vbLf & .Description
        End Select
    End With
End Sub



  

Betrifft: AW: Pivot Tabellen_Feld_Intems von: lee
Geschrieben am: 10.11.2014 11:44:05

Hallo Franz,

vielen Dank für deine Hilfe.

Die Variante 1 kann ich für meinen Report verwenden. Habe auch meinen eigenen Code wie folgt angepasst:


Sub Pivot ()

Dim ws As Worksheet
Dim pt As PivotTable
Dim strJahrKw as String


strJahrKw= 201445


For Each ws In ActiveWorkbook.Worksheets
  For Each pt In ws.PivotTables
    For Each pf In pt.VisibleFields
        If pf = "AUFTRAG_WK" Then
          With pt.PivotFields("AUFTRAG_WK")
            .AutoSort xlAscending, "AUFTRAG_WK"
            .PivotItems(strJahrKw - 1).Visible = True
            .PivotItems(strJahrKw - 2).Visible = True
            .PivotItems(strJahrKw - 3).Visible = True
            .PivotItems(strJahrKw - 14).Visible = False
          End With
        End If
     Next 'PF
  Next  'pt
Next 'ws


End Sub

Wder bei deinem Code noch bei meinem werden die PivotItems z.B. 201444 (201445-1) nicht erkannt.
Bei den beiden Codes wird das Feld "WK" in einer PivotTabelle entdeckt, aber an der Stelle
.PivotItems(intKW - 1).Visible = True
wir es abgebrochen, mit der Fehlermeldung:
Laufzeitfehler 1004:
Die PivotItems-Eigenschaft des PivotField-Objektes kann nicht zugeordnet werden.


Das Item 201444 ist in der PivotTabelle vorhanden und muss eingeblendet werden. Ich verstehe es nicht, warum die Fehlermeldung auftaucht. Habe auch versucht die aktuelle KW (201445) als Integer zu deklarieren, auch dann wird das Item nicht erkannt.

Woran könnte es liegen?

Vielen Dank und Gruß
lee


  

Betrifft: AW: Pivot Tabellen_Feld_Intems von: fcs
Geschrieben am: 10.11.2014 13:06:26

Hallo Lee,

ich hab meine Makros in einer Datei mit mehreren Pivottabellen auf mehreren Blättern gettestet. Es funktionierte.

Wichtig ist natürlich, dass die Pivottabellen vor der Makroausführung aktualisiert werden.
Oder du musst noch eine entsprechende Zeile einbauen.

Außerdem ist es schwierig, die Pivot-Items über die Index-Nr. anzusprechen, besser ist es hier den Namen zu verwenden und es muss sichergestellt sein, dass die Items auch vorhandne sind.

Die Deklaration als Integer funktioniert nicht, da die Werte außerhalb des Wertebereichs für Integerzahlen liegen, wenn Zahlentyp, dann Long verwenden.

Gruß
Franz

Sub Pivot()

Dim ws As Worksheet
Dim pt As PivotTable
Dim strJahrKw As Long


strJahrKw = 201445


For Each ws In ActiveWorkbook.Worksheets
  For Each pt In ws.PivotTables
    pt.RefreshTable
    For Each pf In pt.VisibleFields
        If pf = "AUFTRAG_WK" Then
          With pt.PivotFields("AUFTRAG_WK")
            .AutoSort xlAscending, "AUFTRAG_WK"
            With .PivotItems(Format(strJahrKw - 1, "000000"))
              If .Visible = False Then .Visible = True
            End With
            With .PivotItems(Format(strJahrKw - 2, "000000"))
              If .Visible = False Then .Visible = True
            End With
            With .PivotItems(Format(strJahrKw - 3, "000000"))
              If .Visible = False Then .Visible = True
            End With
            With .PivotItems(Format(strJahrKw - 14, "000000"))
              If .Visible = True Then .Visible = False
            End With
          End With
        End If
     Next 'PF
  Next  'pt
Next 'ws


End Sub