Live-Forum - Die aktuellen Beiträge
Datum
Titel
23.04.2024 08:26:15
Rückgabewert mehrere Suchkriterien Datumsbereich
23.04.2024 08:20:37
VBA: Blatt als pdf per Mail. Blatt mit anderen pdfs vereinen
23.04.2024 08:04:28
4-fach verschachtelte WENN-Funktion
22.04.2024 22:36:13
Marko automatisch ausführen
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1460to1464
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1460to1464
1460to1464
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

VBA - Laufzeitfehler bei Pivot über Makro

VBA - Laufzeitfehler bei Pivot über Makro
02.12.2015 10:45:07
Josef
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

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VBA - Laufzeitfehler bei Pivot über Makro
02.12.2015 11:19:11
fcs
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

Anzeige
Pivot über Makro - Korrektur
02.12.2015 11:24:27
fcs
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

AW: Pivot über Makro - Korrektur
02.12.2015 12:01:15
Josef
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

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

AW: wie das sein kann
02.12.2015 13:20:43
Josef
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

Anzeige
AW: wie das sein kann
02.12.2015 13:32:21
Rudi
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

AW: VBA - Laufzeitfehler bei Pivot über Makro
02.12.2015 11:40:13
Josef
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

Anzeige
AW: VBA - Laufzeitfehler bei Pivot über Makro
02.12.2015 13:32:51
fcs
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

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige