Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1796to1800
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
Inhaltsverzeichnis

VBA Pivottabelle erstellen

VBA Pivottabelle erstellen
01.12.2020 11:29:31
Marin
Hallo Allerseits,
ich habe über Makro aufzeichnen einen Code für Pivottabellenerstellung aufgezeichnet. Der Code funktioniert aber nur einmalig. Schließe ich die Datei oder versuche es in einer weiteren gleichartigen Datei, kommt es zu einem Fehler.
Der Code ist folgender:
'Pivot erstellen
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Tabelle2!R1C1:R144C4", Version:=6).CreatePivotTable TableDestination:= _
"Tabelle1!R3C1", TableName:="PivotTable1", DefaultVersion:=6
Sheets("Tabelle1").Select
Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Kosten"), "Summe von Kosten", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Kostenarten")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Empfänger/Zahlungspflichtiger")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Valuta")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("Valuta").AutoGroup
ActiveSheet.PivotTables("PivotTable1").PivotFields("Monate").Orientation = _
xlHidden
Range("A4").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Kostenarten").ShowDetail = _
False
Range("B6").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Summe von Kosten")
.NumberFormat = "#,##0.00 €;[Red]-#,##0.00 €"
End With
Range("B11").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Kostenarten").AutoSort _
xlDescending, "Summe von Kosten", ActiveSheet.PivotTables("PivotTable1"). _
PivotColumnAxis.PivotLines(1), 1
Range("F18").Select
Ich habe lediglich "NumberFormat = "#,##0.00 €;[Red]-#,##0.00 €"" vom Original geändert. Das läuft dann auch einmalig.
Der Debugger springt dann zu:
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Tabelle2!R1C1:R144C4", Version:=6).CreatePivotTable TableDestination:= _
"Tabelle1!R3C1", TableName:="PivotTable1", DefaultVersion:=6
Ich bitte um Hilfe. Dank im Voraus.
Grüße
Marin

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VBA Pivottabelle erstellen
01.12.2020 14:12:01
fcs
Hallo Marin,
ich habe dein Makro mal in eine allgemeinere Form umgeschrieben.
Dabei werden keine Namen für Tabellenblätter und den Pivotbericht vorgegeben sondern es werden Objekt-Variablen verwendet, denen die Objekte (Tabellenblätter, Pivot-Bericht) zugewiesen werden.
So sollte das Makro rund laufen. Die Spaltentitel/Namen der Pivotfelder in den 4 Spalten müssen aber in allen Blättern mit den Quelldaten identisch sein.
LG
Franz

Sub Make_Pivot()
'Pivot erstellen
Dim wksData As Worksheet
Dim wksPivot As Worksheet, pvTab As PivotTable
Dim lngZeile_L As Long
Set wksData = ActiveSheet 'Tabellenblatt mit den Quelldaten
'oder
'Set wksData = ActiveWorkbook.Worksheets("Tabelle2") 'Name des Tabellenblatts ggf. anpassen
If MsgBox("Mit den Daten auf Blatt """ & wksData.Name & """ den Pivotbericht erstellen?", _
vbQuestion + vbYesNo, _
"P I V O T - T A B E L L E N B E R I C H T   E R S T E L L E N") = vbNo Then Exit Sub
With wksData
'letzte Zeile mit Daten
lngZeile_L = .UsedRange.Row + .UsedRange.Rows.Count - 1
End With
Sheets.Add
Set wksPivot = ActiveSheet 'Tabellenblatt mit dem Pivotbericht
'Pivotbericht anlegen
Set pvTab = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=wksData.Name & "!R1C1:R" & lngZeile_L & "C4", _
Version:=6).CreatePivotTable(TableDestination:=wksPivot.Name & "!R3C1", _
TableName:="PivotTable" & (wksPivot.PivotTables.Count + 1), DefaultVersion:=6)
wksPivot.Select
With pvTab
.AddDataField .PivotFields("Kosten"), "Summe von Kosten", xlSum
With .PivotFields("Kostenarten")
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("Empfänger/Zahlungspflichtiger")
.Orientation = xlRowField
.Position = 2
End With
With .PivotFields("Valuta")
.Orientation = xlRowField
.Position = 2
End With
.PivotFields("Valuta").AutoGroup
.PivotFields("Monate").Orientation = xlHidden
.PivotFields("Kostenarten").ShowDetail = False
With .PivotFields("Summe von Kosten")
.NumberFormat = "#,##0.00 €;[Red]-#,##0.00 €"
End With
.PivotFields("Kostenarten").AutoSort xlDescending, "Summe von Kosten", _
.PivotColumnAxis.PivotLines(1), 1
End With
End Sub

Anzeige
AW: VBA Pivottabelle erstellen
02.12.2020 15:09:51
Marin
Hallo Franz,
ich habe deinen Code 1:1 übernommen. Leider bricht auch hier alles ab und der Debugger springt auf
Set pvTab = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=wksData.Name & "!R1C1:R" & lngZeile_L & "C4", _
Version:=6).CreatePivotTable(TableDestination:=wksPivot.Name & "!R3C1", _
TableName:="PivotTable" & (wksPivot.PivotTables.Count + 1), DefaultVersion:=6)
Zum Verständnis: Meine Quelldateien werden aus einer Tabelle1 in eine Tabelle2 exportiert. Dort stehen sie und das soll auch die Quelle für die Pivot sein. Das ist auch das aktive Worksheet beim Start deines Codes. Ich bin daher bei " Set wksData = ActiveSheet 'Tabellenblatt mit den Quelldaten" geblieben.
Falls Du noch eine Idee hast, danke.
Grüße
Anzeige
AW: VBA Pivottabelle erstellen
03.12.2020 00:29:22
fcs
Hallo Marin,
hier ist meine Datei in der Ich das Makro überarbeitet/getestet habe.
https://www.herber.de/bbs/user/142024.xlsm
Die Reihenfolge der 4 Spalten spielt dabei keine Rolle.
Um die Ursache für den Fehler zu finden bräuchte ich eine Beispieldatei mit ggf. anonymisierten Daten.
LG
Franz
AW: VBA Pivottabelle erstellen
04.12.2020 08:59:34
Marin
Hallo Franz,
sowohl bei deiner, als auch bei meiner anliegenden Datei, springt der Debugger an diese Stelle:
'Pivotbericht anlegen
Set pvTab = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=wksData.Name & "!R1C1:R" & lngZeile_L & "C4", _
Version:=6).CreatePivotTable(TableDestination:=wksPivot.Name & "!R3C1", _
TableName:="PivotTable" & (wksPivot.PivotTables.Count + 1), DefaultVersion:=6)
Dein Code klappt bis zu dem Abfragefenster ob ich mit der Tabelle so und so die Pivot erstellen will. Ich habe also die Pivotgrundlage mal upgeloadet um es Dir einfacher zu machen den Fehler zu finden. Für mich ist das alles zu hoch. Bin seit einigen Wochen in der Materie. Gestern lief - ich kann es mir nicht erklären - dein Code durch und ich hatte eine Pivot nach meinen Vorstellungen. Leider war bei den Kostenarten aber ein Feld "Leer" ohne Inhalt. Ich konnte dieses aber in der Pivotgrundlage nicht finden. Es war dort keine Zeile markiert, die keinen Inhalt hatte. Ich hatte gehoft, dass wir das heute thematisieren können. Leider setzt das Problem jedoch wieder weiter vorne an.
Vielen Dank im Voraus.
Grüße
Marin
https://www.herber.de/bbs/user/142051.xlsx
Anzeige
AW: VBA Pivottabelle erstellen
05.12.2020 19:18:49
fcs
Hallo Marin,
ich habe viel rumprobiert.
Die letzte Zeile wird jetzt anders berechnet. Scheinbar gibt es unterhalb der Daten irgendwie formatierte Zellen, sodass der Weg über UsedRange nicht die letzte ausgefüllte Zeile sondern die letzte benutzte Zeile ermittelt.
Ich hab keine Ahnung, warum Autogroup für Feld Valuta hier im Makro nicht nicht funktioniert.
Dadurch gibt es dann einen Folgefehler, da das Feld Monate jetzt nicht vorhanden ist.
Manuell kann man die Gruppierung ja durchführen.
Ich hab im Makro mal alle Problemzeilen deaktiviert.
LG
Franz
Sub Make_Pivot_Neu()
'Pivot erstellen
Dim wksData As Worksheet
Dim wksPivot As Worksheet, pvTab As PivotTable
Dim lngZeile_L As Long
Set wksData = ActiveSheet 'Tabellenblatt mit den Quelldaten
'oder
'Set wksData = ActiveWorkbook.Worksheets("Tabelle2") 'Name des Tabellenblatts ggf. anpassen
If MsgBox("Mit den Daten auf dem Blatt """ & wksData.Name & """ den Pivotbericht erstellen?",  _
_
vbQuestion + vbYesNo, _
"P I V O T - T A B E L L E N B E R I C H T   E R S T E L L E N") = vbNo Then Exit Sub
With wksData
'letzte Zeile mit Daten
lngZeile_L = .Cells(.Rows.Count, 1).End(xlUp).Row
'    lngZeile_L = .UsedRange.Row + .UsedRange.Rows.Count - 1
End With
Sheets.Add
Set wksPivot = ActiveSheet 'Tabellenblatt mit dem Pivotbericht
'Pivotbericht anlegen
'  Set pvTab = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=wksData.Name & "!R1C1:R" & lngZeile_L & "C4", _
Version:=6).CreatePivotTable(TableDestination:=wksPivot.Name & "!R3C1", _
TableName:="PivotTable" & (wksPivot.PivotTables.Count + 1), DefaultVersion:=6)
Set pvTab = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=wksData.Name & "!R1C1:R" & lngZeile_L & "C4").CreatePivotTable( _
TableDestination:=wksPivot.Name & "!R3C1", _
TableName:="PivotTable" & (wksPivot.PivotTables.Count + 1))
wksPivot.Select
With pvTab
.AddDataField .PivotFields("Kosten"), "Summe von Kosten", xlSum
With .PivotFields("Summe von Kosten")
.NumberFormat = "#,##0.00 €;[Red]-#,##0.00 €"
End With
With .PivotFields("Kostenarten")
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("Empfänger/Zahlungspflichtiger")
.Orientation = xlRowField
.Position = 2
End With
With .PivotFields("Valuta")
.Orientation = xlRowField
.Position = 2
End With
'Datum nach Monate und Tage gruppieren
'      .PivotFields("Valuta").AutoGroup    'Probbleme, funktioniert nicht
'      .PivotFields("Valuta").Group Start:=True, End:=True, Periods:=Array(False, False, _
False, True, True, False, False) 'funktioniert auch nicht
'      .PivotFields("Monate").Orientation = xlHidden 'Folgefehler, da AutoGroup für Valuta  _
nicht funktiniert
.PivotFields("Kostenarten").ShowDetail = False
.PivotFields("Kostenarten").AutoSort xlDescending, "Summe von Kosten", _
.PivotColumnAxis.PivotLines(1), 1
End With
End Sub

Anzeige
AW: VBA Pivottabelle erstellen
01.12.2020 14:27:15
Yal
Hallo Marin,
es liegt daran, dass solang Excel offen ist, jede Pivottabelle, die herstellt wird, bekommt ein fortlaufenden Namen "Pivottable1", "Pivottable2", usw.
Du muss Variablen verweden, um die Element bei der Herstellung abzufangen und wiederverwenden zu können:

Sub Pivottabelle_herstellen()
Dim aws As Worksheet 'ActiveSheet
Dim pws As Worksheet 'Pivot-Worksheet
Dim PC As PivotCache
Dim PT As PivotTable
Const rngQuelle = "R1C1:R144C4"
Const rngZiel = "R3C1"
Set aws = ActiveSheet
Set pws = ActiveWorkbook.Sheets.Add
Set PC = ActiveWorkbook.PivotCaches.Create(SourceData:=aws & "!" & rngQuelle)
Set PT = PC.CreatePivotTable(TableDestination:=pws & "!" & rngZiel)
PT.AddDataField PT.PivotFields("Kosten"), "Summe von Kosten", xlSum
PT.PivotFields("Summe von Kosten").NumberFormat = "#,##0.00 €;[Red]-#,##0.00 €"
PT.PivotFields("Kostenarten").Orientation = xlRowField
PT.PivotFields("Kostenarten").Position = 1
PT.PivotFields("Kostenarten").ShowDetail = False
PT.PivotFields("Kostenarten").AutoSort xlDescending, "Summe von Kosten", PT.PivotColumnAxis. _
PivotLines(1), 1
PT.PivotFields("Empfänger/Zahlungspflichtiger").Orientation = xlRowField
PT.PivotFields("Empfänger/Zahlungspflichtiger").Position = 2
PT.PivotFields("Valuta").Orientation = xlRowField
PT.PivotFields("valuta").Position = 2
PT.PivotFields("Valuta").AutoGroup
End Sub
Ungetestet. Es geht nur um den Art und Weise zu programmieren.
Position = 2 kommt zweimal vor. Du kannst die Position durch der Reihenfolge der Aufruf festlegen. Dann sparst Du dir die Zeile für die Positionierung (je weniger, desto besser). Einfach probieren.
Noch ein Tipp: Euro-Zeichen im Zahlenformat weg. Das ist Excel-Zahlenformat für Anfänger. Es belastet die Lesbarkeit und bringt sehr wenig Mehrwert. Höchsten irgendwo "Alle Werte in EUR".
(Wer nicht weißt, worum es geht, hat in deiner Auswertung nichts zu suchen ;-)
Viel Erfolg
Yal
Anzeige
AW: VBA Pivottabelle erstellen
02.12.2020 15:14:31
Marin
Hi Yal,
danke erstmal. Ich kann Dir leider nicht ganz folgen.
1:1 führt dein Code zum Abbruch bei
Set PC = ActiveWorkbook.PivotCaches.Create(SourceData:=aws & "!" & rngQuelle).
Trotzdem Danke.
Grüße
AW: VBA Pivottabelle erstellen
02.12.2020 16:49:27
Yal
Hallo Marin,
ja, sorry zu stark gekürzt.
Die zwei Code-Zeilen set PC und Set PT in folgende zusammenführen
    Set PC = ActiveWorkbook.PivotCaches.Create(SourceData:=aws & "!" & rngQuelle).CreatePivotTable(TableDestination:=pws & "!" & rngZiel)
Viel Erfolg
Yal
AW: VBA Pivottabelle erstellen
04.12.2020 09:00:48
Marin
Hallo Yal,
leider wird auch die Zeile "Set PC..." vom Debugger aufgegriffen.
Ich habe im Dialog mit Franz meine Pivotgrundlage hochgeladen. Falls Du noch dran bleiben willst, schau Dir bitte die Datei an.
Ich danke im Voraus.
Grüße
Marin
Anzeige
AW: VBA Pivottabelle erstellen
04.12.2020 09:21:19
Yal
sorry, übersehen.
set PT =
wäre richtig
PC ist als Typ PivotCache
PT ist als Typ PivotTable
deklariert.
VG
Yal

327 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige