VBA - Laufzeitfehler bei Pivot über Makro

Bild

Betrifft: VBA - Laufzeitfehler bei Pivot über Makro
von: Josef
Geschrieben am: 02.12.2015 10:45:07

Hallo allerseits,
ich habe ein Makro geschrieben, welches zu jedem Tabellenblatt einer Arbeitsmappe eine Pivot-Tabelle erstellen soll. Es sieht so aus:

Sub Pivot()
Dim myWorksheet As Worksheet
Dim Bereich As Range
For Each myWorksheet In Worksheets
    Set Bereich = ActiveSheet.UsedRange
    
    Range(Bereich.Address).Select
    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
    Bereich, TableName:="Pivot"
    
    With ActiveSheet.PivotTables("Pivot")
        .PivotFields("Projektnr.").Orientation = xlRowField
        .PivotFields("Betrag").Orientation = xlDataField
        
        With ActiveSheet.PivotTables("Pivot").PivotFields("Anzahl von Betrag")
            .Caption = "Summe von Betrag"
            .Function = xlSum
            .NumberFormat = "#.##0,00"
        End With
        
    End With
    
Next
End Sub
Nun erhalte ich nach der Durchführung einen Laufzeitfehler, den ich leider nicht ganz verstehe (bzw. ich weiß nicht wie ich es beheben kann):
Laufzeitfehler '1004':
 Der PivotTable-Feldname ist ungültig. Um einen PivotTable-Bericht zu erstellen,
 müssen Sie Daten verwenden, die in einer Liste mit Spaltenüberschriften
 organisiert sind. Wenn Sie den Namen eines PivotTable-Berichtsfeld ändern, 
 müssen Sie einen neuen Namen für das Feld eingeben.

Das Makro erstellt übrigens wie gewünscht die Pivot - ein mal, dann kommt der Laufzeitfehler. Im Grunde möchte ich, dass die Pivot-Tabelle so heißt, wie ihre Quelltabelle, nur ergänzt um das Wort "Pivot".
Ich bin für jede Hilfe dankbar. Bis dahin alles Gute und beste Grüße.
Josef

Bild

Betrifft: AW: VBA - Laufzeitfehler bei Pivot über Makro
von: fcs
Geschrieben am: 02.12.2015 11:19:11
Hallo Josef,
probier es mal so, damit der Bezug zum jeweils aktiven Tabellenblatt stimmt.
Gruß
Franz

Sub Pivot()
  Dim myWorksheet As Worksheet
  Dim Bereich As Range
  
  For Each myWorksheet In Worksheets
      myWorksheet.Activate
      Set Bereich = ActiveSheet.UsedRange
      
      Range(Bereich.Address).Select
      ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
      Bereich, TableName:="Pivot_" & myWorksheet.Name
      
      ActiveSheet.Name = "Pivot-" & myWorksheet.Name
      With ActiveSheet.PivotTables(1)
          .PivotFields("Projektnr.").Orientation = xlRowField
          .PivotFields("Betrag").Orientation = xlDataField
          
          With ActiveSheet.PivotTables(1).PivotFields("Anzahl von Betrag")
              .Caption = "Summe von Betrag"
              .Function = xlSum
              .NumberFormat = "#.##0,00"
          End With
          
      End With
      
  Next
End Sub


Bild

Betrifft: Pivot über Makro - Korrektur
von: fcs
Geschrieben am: 02.12.2015 11:24:27
Hallo Josef,
du muss noch die Anweisung für das Zahlenformat korrigieren.
VBA möchte hier die Schreibweise im US-Format für 1000er-Stelle und Dezimalzeichen.

              .NumberFormat = "#,##0.00"

Gruß
Franz

Bild

Betrifft: AW: Pivot über Makro - Korrektur
von: Josef
Geschrieben am: 02.12.2015 12:01:15
Hi Franz,
du hast Recht, habe das Zahlenformat nun korrigiert.
Dennoch erhalte ich nach wie vor die Fehlermeldung:
https://www.herber.de/forum/messages/1461894
Etwas ist mir aufgefallen, was ich leider nicht erklären kann:
Wenn ich folgende Zeilen rauskommentiere:

'    With ActiveSheet.PivotTables(1).PivotFields("Anzahl von Betrag")
'        .Caption = "Summe von Betrag"
'        .Function = xlSum
'        .NumberFormat = "#,##0.00"
'    End With
        
'    With ActiveSheet.PivotTables(1).PivotFields("Projektnr.")
'        .PivotItems("(blank)").Visible = False
'    End With
.. so ist das Format der ersten erstellten Pivot unverändert. Alle weiteren Pivots haben aber interessanterweise das richtige Format (statt "Anzahl von Betrag" steht dort "Summe von Betrag" usw.), obwohl es dazu keine Anweisung gab.
Wie kann das sein?
Gruß
Josef

Bild

Betrifft: wie das sein kann
von: Rudi Maintaire
Geschrieben am: 02.12.2015 13:01:59
Hallo,
Es wird immer die Summe gebildet wenn die Daten einer Spalte ausschließlich 'richtige' Zahlen sind.
Gruß
Rudi

Bild

Betrifft: AW: wie das sein kann
von: Josef
Geschrieben am: 02.12.2015 13:20:43
Hallo zusammen,
ich habe die Ursache gefunden.
Die UsedRange beinhaltete auch Zellen, die gar keine Daten enthielten. Sobald der Datenbereich der Pivot leere Zellen beinhaltet, werden diese in der Pivot als (Leer) angezeigt. Dadurch wird in der Pivot im Werte-Bereich nicht "Summe von Betrag" sondern automatisch "Anzahl von Betrag" erstellt.
Die UsedRange musste also zur "wirklichen" UsedRange werden, damit es funktioniert. Und das tut es jetzt:
Hier mein Makro:

Sub Pivot()
'Dieses Makro soll nur eingesetzt werden, nachdem in der Umsatz-Datei Intermodal die KWs ergä _
nzt wurden (auch dafür gibt es ein Makro).'
Dim myWorksheet As Worksheet
Dim Bereich As Range
'Die Variablen werden deklariert. Es gibt nur zwei Stück. myWorksheet bezieht sich auf das  _
gerade aktive Tabellenblatt, Bereich ist der benutzte Zellenbereich in diesem.
For Each myWorksheet In Worksheets
On Error Resume Next
'Das Makro durchläuft alle Tabellenblätter (myWorksheet), etwaige Fehlermeldungen werden  _
ignoriert.
    myWorksheet.Activate
    Set Bereich = RealUsedRange
    'Die Variable Bereich wird durch die Funktion RealUsedRange (s.u.) ermittelt.
    
    Range(Bereich.Address).Select
    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
    Bereich, TableName:="Pivot_" & myWorksheet.Name
    'Die RealUsedRange wird ausgewählt, dann wird eine Pivot daraus erstellt. Sie erhält einen  _
Namen.'
    
    
    ActiveSheet.Name = "Pivot " & myWorksheet.Name
    ActiveSheet.Tab.ColorIndex = 6
    'Das Tabellenblatt erhält einen Namen und wird eingefärbt.'
    
    With ActiveSheet.PivotTables(1)
        .PivotFields("Projektnr.").Orientation = xlRowField
        .PivotFields("Betrag").Orientation = xlDataField
        .TableStyle2 = "PivotStyleLight16"
        .DisplayNullString = False
        'Die Struktur und das Format der Pivot wird angepasst.'
    End With
        
    With ActiveSheet.PivotTables(1).PivotFields("Summe von Betrag")
        .Function = xlSum
        .NumberFormat = "#,##0.00"
        'Das Zahlenformat der Zahlen in "Summe von Betrag" wird noch angepasst."
    End With
 
    
Next
End Sub
Public Function RealUsedRange() As Range
     
    Dim FirstRow        As Long
    Dim LastRow         As Long
    Dim FirstColumn     As Integer
    Dim LastColumn      As Integer
     'Die deklarierten Variablen bilden die Eckpunkte der RealUsedRange.'
    On Error Resume Next
     
    FirstRow = Cells.Find(What:="*", After:=Range("IV65536"), LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
     
    FirstColumn = Cells.Find(What:="*", After:=Range("IV65536"), LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column
     
    LastRow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
     
    LastColumn = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
     
    Set RealUsedRange = Range(Cells(FirstRow, FirstColumn), Cells(LastRow, LastColumn))
     
    On Error GoTo 0
     
End Function
Nochmal vielen Dank Franz und Rudi.
Beste Grüße
Josef

Bild

Betrifft: AW: wie das sein kann
von: Rudi Maintaire
Geschrieben am: 02.12.2015 13:32:21
Hallo,
wenn du eine richtige Tabelle ohne komplett leere Zeilen hast, kannst du den Bereich mit Range("A1").CurrentRegion ermitteln.
Und das

Range(Bereich.Address).Select

ist Quatsch.
Wenn du überflüssigerweise selektieren willst, reicht
Bereich.Select

vollkommen aus.
Gruß
Rudi

Bild

Betrifft: AW: VBA - Laufzeitfehler bei Pivot über Makro
von: Josef
Geschrieben am: 02.12.2015 11:40:13
Hallo Franz,
vielen Dank für deine Hilfe, ich bin damit den Fehler losgeworden. Leider ist nun ein anderer aufgetaucht.
Der Code sieht nun folgendermaßen aus (ich habe noch ein paar Tabellenformatierungen eingebaut, wahrscheinlich liegt da das Problem):

Sub Pivot()
Dim myWorksheet As Worksheet
Dim Bereich As Range
For Each myWorksheet In Worksheets
    myWorksheet.Activate
    Set Bereich = ActiveSheet.UsedRange
    
    Range(Bereich.Address).Select
    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
    Bereich, TableName:="Pivot_" & myWorksheet.Name
    
    ActiveSheet.Name = "Pivot " & myWorksheet.Name
    
    With ActiveSheet.PivotTables(1)
        .PivotFields("Projektnr.").Orientation = xlRowField
        .PivotFields("Betrag").Orientation = xlDataField
        .TableStyle2 = "PivotStyleLight16"
    End With
        
    With ActiveSheet.PivotTables(1).PivotFields("Anzahl von Betrag")
        .Caption = "Summe von Betrag"
        .Function = xlSum
        .NumberFormat = "#,##0"
    End With
        
    With ActiveSheet.PivotTables(1).PivotFields("Projektnr.")
        .PivotItems("(blank)").Visible = False
    End With
    
    
Next
End Sub
Das erste Tabellenblatt wird noch normal durchlaufen, beim zweiten aber erhalte ich folgende Fehlermeldung:
Laufzeitfehler '1004':
Die PivotFields-Eigenschaft des PivotTable-Objektes kann nicht zugeordnet werden. 
Laut VB-Editor liegt der Fehler in der fetten Zeile (s.o.). Das Tabellenblatt ist identisch mit dem ersten, was den Aufbau angeht.
Vorschläge?
Beste Grüße
Josef

Bild

Betrifft: AW: VBA - Laufzeitfehler bei Pivot über Makro
von: fcs
Geschrieben am: 02.12.2015 13:32:51
Hallo Josef,
in der betreffenden Pivot-Tabelle gibt es zu PivotField("Projektnr.") das Pivot-Item "(blank)" nicht. Deshalb der Fehler.
Hier muss du eine entsprechende Fehlerbehandlung einbauen.
Das Summen-Feld für Betrag kann man auch direkt erstellen - ohne den Umweg,der Umbenennung wenn Excel als automatische Fuktion Anzahl wählt.
Gruß
Franz

Sub Pivot_2()
    Dim myWorksheet As Worksheet
    Dim Bereich As Range
    Dim pvTab As PivotTable
    
    For Each myWorksheet In Worksheets
        myWorksheet.Activate
        Set Bereich = ActiveSheet.UsedRange
        
        Range(Bereich.Address).Select
        ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
        Bereich, TableName:="Pivot_" & myWorksheet.Name
        
        ActiveSheet.Name = "Pivot " & myWorksheet.Name
        
        Set pvTab = ActiveSheet.PivotTables(1)
        
        With pvTab
            .PivotFields("Projektnr.").Orientation = xlRowField
            .AddDataField Field:=.PivotFields("Betrag"), Caption:="Summe von Betrag", _
                  Function:=xlSum
            .TableStyle2 = "PivotStyleLight16"
        End With
            
        With pvTab.DataFields("Summe von Betrag")
            .NumberFormat = "#,##0"
        End With
            
        With pvTab.PivotFields("Projektnr.")
            On Error Resume Next
            .PivotItems("(blank)").Visible = False
            Err.Clear
        End With
    Next
End Sub


 Bild

Beiträge aus den Excel-Beispielen zum Thema "VBA - Laufzeitfehler bei Pivot über Makro"