Pivottabelle über VBA erstellen

Forumthread: Pivottabelle über VBA erstellen

26.01.2017 08:59:11

Thomas

Guten Morgen zusammen,
ich habe ein Problem beim Erstellen einer Pivottabelle.
Mein Workbook besteht aus den Tabellen "Januar Grafik" und "Januar Daten".
Die Pivottabelle soll in der Tabelle "Januar Grafik" erstellt werden - Datenquelle soll "Januar Daten" sein.
Wenn ich über den Makrorecorder das Erstellen der Pivottabelle aufzeichne, kommt folgender Code _
heraus:


Sheets("Januar Grafik").Select
Range("A1").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Januar Daten!R1C2:R1048576C3", Version:=xlPivotTableVersion15). _
CreatePivotTable TableDestination:="Januar Grafik!R1C1", TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion15
Sheets("Januar Grafik").Select
Cells(1, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable2").PivotFields("RT")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("PNR"), "Anzahl von PNR", xlCount
With ActiveSheet.PivotTables("PivotTable2").PivotFields("RT")
.PivotItems("  ").Visible = False
.PivotItems("(blank)").Visible = False
End With
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("'Januar Grafik'!$A$1:$B$6")
ActiveChart.FullSeriesCollection(1).Select
ActiveChart.FullSeriesCollection(1).ApplyDataLabels

Beim anschließenden Ausführen des Codes bleibt er allerdings in der Zeile


ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Januar Daten!R1C2:R1048576C3", Version:=xlPivotTableVersion15). _
CreatePivotTable TableDestination:="Januar Grafik!R1C1", TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion15

stehen. Auch wenn ich die Tabelle lösche und wieder Neuerstelle.
Fehlermeldung:


Laufzeitfehler '5' Ungültiger Prozeduraufruf oder ungültiges Argument

Macht es einen Unterschied, ob hinter SourceData=


Januar Grafik!R1C1"

oder



"'Januar Grafik'!$A$1"

steht?
Vielen Dank schonmal für Eure Antworten!
Gruß Thomas

1
Beitrag zum Forumthread

Beitrag zu diesem Forumthread

Betreff

Datum

Anwender

AW: Pivottabelle über VBA erstellen

26.01.2017 19:42:55

fcs

AW: Pivottabelle über VBA erstellen

26.01.2017 19:42:55

fcs

Hallo Thomas,
Blattnamen, die Leerzeichen oder andere Sonderzeichen enthalten, müssen bei Formeln immer in Hochkommata eingefast werden.
Ich hab dein Makro mal mit zusätzlichen Objektvariablen versehen. Das macht das Makro etwas übersichtlicher.
Leider ist der Makrorecorder manchmal etwas fehlerhaft. Die erforderliche Schreibweise für die Zelladressen (A1 oder R1C1) kann im Code für Makros für Pivot-Tabellen variieren.
Ich hab dein Makro unter Excel 2010 getestet. Da funktioniert einiges nicht wegen neuerer Funktionen.
Ich hab das Makro zum Erzeugen des Pivotberichts in eine etwas allgemeinere Form umgestellt.
Vor dem Ausführen des Makros musst einen im Blatt "Januar Grafik" schon vorhandenen Pivot-Bericht löschen.
LG
Franz

Sub AATest()
Dim wksMonat As Worksheet, wksGrafik As Worksheet
Dim pvTab As PivotTable
Set wksGrafik = Worksheets("Januar Grafik")
Set wksMonat = Worksheets("Januar Daten")
wksGrafik.Select
Range("A1").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"'" & wksMonat.Name & "'!R1C2:R1048576C3"). _
CreatePivotTable TableDestination:="'" & wksGrafik.Name & "'!R1C1"
Set pvTab = wksGrafik.PivotTables(1)
ActiveWorkbook.ShowPivotTableFieldList = True
With pvTab.PivotFields("RT")
.Orientation = xlRowField
.Position = 1
End With
pvTab.AddDataField pvTab.PivotFields("PNR"), "Anzahl von PNR", xlCount
With pvTab.PivotFields("RT")
.PivotItems("  ").Visible = False
.PivotItems("(blank)").Visible = False
End With
'Nachfolgende Zeilen zum Erstellen eines Diagramms funktionieren unter Excel 2010 nicht
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("'Januar Grafik'!$A$1:$B$6")
ActiveChart.FullSeriesCollection(1).Select
ActiveChart.FullSeriesCollection(1).ApplyDataLabels
End Sub

Schritt-für-Schritt-Anleitung

Um eine Pivottabelle über VBA zu erstellen, kannst du den folgenden Code verwenden. Dieser Code ist für Excel 2010 optimiert, aber auch in neueren Versionen anwendbar.

Sub AATest()
    Dim wksMonat As Worksheet, wksGrafik As Worksheet
    Dim pvTab As PivotTable
    Set wksGrafik = Worksheets("Januar Grafik")
    Set wksMonat = Worksheets("Januar Daten")

    wksGrafik.Select
    Range("A1").Select

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "'" & wksMonat.Name & "'!R1C2:R1048576C3"). _
    CreatePivotTable TableDestination:="'" & wksGrafik.Name & "'!R1C1"

    Set pvTab = wksGrafik.PivotTables(1)
    ActiveWorkbook.ShowPivotTableFieldList = True

    With pvTab.PivotFields("RT")
        .Orientation = xlRowField
        .Position = 1
    End With

    pvTab.AddDataField pvTab.PivotFields("PNR"), "Anzahl von PNR", xlCount

    With pvTab.PivotFields("RT")
        .PivotItems("  ").Visible = False
        .PivotItems("(blank)").Visible = False
    End With

    ' Diagramm erstellen (unter Excel 2010 funktionieren diese Zeilen nicht)
    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
    ActiveChart.SetSourceData Source:=Range("'Januar Grafik'!$A$1:$B$6")
    ActiveChart.FullSeriesCollection(1).Select
    ActiveChart.FullSeriesCollection(1).ApplyDataLabels
End Sub

Häufige Fehler und Lösungen

Ein häufiger Fehler, der beim Erstellen einer Pivottabelle mit VBA auftritt, ist der Laufzeitfehler '5', der auf einen ungültigen Prozeduraufruf hinweist. Dies kann durch falsche Zelladressen oder nicht vorhandene Tabellen verursacht werden.

Lösung: Achte darauf, dass Blattnamen mit Leerzeichen in Hochkommata eingeschlossen sind. Beispiel:
```
SourceData:="'Januar Daten'!R1C2:R1048576C3"
```
Problem: Fehler bei der Datenquelle. Wenn SourceData nicht korrekt definiert ist, wird die Pivottabelle nicht erstellt. Stelle sicher, dass die Quelle die korrekten Zellreferenzen enthält.

Alternative Methoden

Wenn du keine VBA-Programmierung verwenden möchtest, kannst du auch die Pivot-Tabelle über das Excel-Menü erstellen.

Wähle die gewünschten Daten aus.
Gehe zu „Einfügen“ > „PivotTable“.
Wähle den Speicherort für die Pivottabelle aus und klicke auf „OK“.
Konfiguriere die Felder im PivotTable-Feldbereich.

Diese Methode ist benutzerfreundlich, wenn du nicht mit VBA vertraut bist.

Praktische Beispiele

Ein praktisches Beispiel für das Erstellen einer Pivottabelle in Excel könnte die Analyse von Verkaufsdaten sein. Angenommen, du hast folgende Daten:

RT	PNR	Anzahl
A	001	10
B	002	20
A	003	15

Mit dem VBA-Code kannst du schnell eine Pivottabelle erstellen, die die Anzahl der PNR nach RT gruppiert.

Tipps für Profis

Nutze Objektvariablen wie im Beispiel gezeigt, um den Code übersichtlicher zu gestalten.
Vermeide die Verwendung von Select und Activate, um die Ausführungsgeschwindigkeit zu erhöhen.
Überprüfe die Kompatibilität von Funktionen, wenn du mit verschiedenen Excel-Versionen arbeitest, insbesondere bei Pivot Charts.

FAQ: Häufige Fragen

1. Wie kann ich eine Pivottabelle aus einer anderen Pivottabelle erstellen?
Du kannst eine neue Pivottabelle aus einer bestehenden Pivottabelle erstellen, indem du die Datenquelle auf die erste Pivottabelle setzt.

2. Welches Excel-Format ist für VBA geeignet?
VBA-Code funktioniert in Excel 2010 und neueren Versionen, jedoch können einige Funktionen in älteren Versionen fehlen oder unterschiedlich sein.

3. Was ist der Unterschied zwischen R1C1 und A1 Notation?
R1C1 verwendet Zeilen- und Spaltennummern, während A1 Buchstaben für Spalten und Zahlen für Zeilen nutzt. Beide Formate können in VBA verwendet werden, je nach Vorliebe.

Beliebteste Forumthreads (12 Monate)

Pivottabelle über VBA erstellen

Forumthread: Pivottabelle über VBA erstellen

1 Beitrag zum Forumthread Beitrag zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial