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

Erstellung Pivottabelle per Makro

Erstellung Pivottabelle per Makro
31.03.2015 15:04:30
Carlu
Hallo zusammen,
ich möchte gerne per Makro eine Pivottabelle in Excel 2010 erzeugen. Dabei hatte ich ein Makro aufgezeichnet und auf dieser Grundlage bisher folgenden Code hinterlegt:
Columns("A:AW").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Materialliste!R1C1:R1048576C49", Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="Tabelle1!R3C1", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion14
Sheets("Tabelle1").Select
Cells(3, 1).Select
Die Rohdaten befinden sich als Auflistung in dem Tabellenblatt Materialliste, die Pivottabelle wird in ein neues Tabellenblatt namens Tabelle1 hinterlegt als PivotTable1. Wenn das Makro mit neuer Datei, die die Rohdaten enthält, ausgeführt wird, gibt Excel nun folgenden Fehler raus:
Laufzeitfehler '1004':
Bezug ist ungültig.
Ich finde ehrlich gesagt seit 3 Stunden keinen Fehler. Wenn ich die Pivottabelle dann wieder manuell erzeuge und die Makroaufzeichnung mitlaufen lasse, wird genau dieser obige Code von Excel angegeben! Mittlerweile bin ich hier völlig ratlos woran es liegen könnte! Eine Hilfestellung wäre großartig! Vielen Dank schon mal vorab!
Grüße
Carlu

3
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Erstellung Pivottabelle per Makro
31.03.2015 16:34:17
fcs
Hallo Carlu,
Pivotberichte haben unter VBA so ihre Tücken.
Soweit möglich sollte man man mit Objektvariablen arbeiten und diesen dann jeweils die passenden Objekte zuweisen. Im cufgezeichneten Code muss man dann die aufgezeichneten Namen und Zellbereiche durch die aus den Objektvariablen ermittelten Werte ersetzen.
Das betrifft hier dann die Tabellennamen, den pivot-Tabellennamen und ggf. den Datenbereich.
Nachfolgend ein entsprechend modifiziertes Makro.
Bis zur Zeile "Exit Sub" generiert es den leeren Pivot-Bericht.
Danach hab ich Beispiele angefügt, wie man dann die Felder und Formatierungen einbauen kann.
Gruß
Franz
Sub MakePivot()
Dim wkb As Workbook
Dim wksData As Worksheet, rngData As Range
Dim wksPivot As Worksheet, pvTab As PivotTable, pvField As PivotField
Dim strFeld As String
Set wkb = ActiveWorkbook
Set wksData = wkb.Worksheets("Materialliste") 'Tabelle mit Datenbasis
wkb.Worksheets.Add after:=wksData
Set wksPivot = ActiveSheet
With wksData
'Datenbereich für Pivotbericht definieren _
- ist ggf. günstiger als komplette Spalten zu nehmen
Set rngData = .Range(.Cells(1, 1), _
.Cells(.UsedRange.Row + .UsedRange.Rows.Count - 1, 49))
wkb.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:="'" & .Name & "'!" & rngData.Address(True, True, xlR1C1), _
Version:=xlPivotTableVersion14).CreatePivotTable _
Tabledestination:="'" & wksPivot.Name & "'!R3C1", _
TableName:="PivotTab1", _
Defaultversion:=xlPivotTableVersion14
End With
wksPivot.Activate
Range("A3").Select
Exit Sub
'Beispiele, wie man die Inhalte des Pivotberichtes konfigurieren kann
'Pivottable Objektvariable zuordnen
Set pvTab = wksPivot.PivotTables(1)
'Pivottabelle konfigurieren
With pvTab
'Pivotbericht-Berichtslayout in Tabellendarstelltung anzeigen
.RowAxisLayout Rowlayout:=xlTabularRow
'Felder im Datenbereich definieren
strFeld = "Summe Zahlenwert" 'Beschriftung/Name des Datenfeldes
.AddDataField .PivotFields("Zahlenwert"), strFeld, xlSum
With .PivotFields(strFeld)
.NumberFormat = "#,##0.00"
End With
strFeld = "Summe Zeitwert"
.AddDataField .PivotFields("Zeitwert"), strFeld, xlSum
With .PivotFields(strFeld)
.NumberFormat = "[h]:mm:ss"
End With
strFeld = "Anzahl Mat_Nr"
.AddDataField .PivotFields("Mat_Nr"), strFeld, xlCount
With .PivotFields(strFeld)
.NumberFormat = "#,##0"
End With
' Zeilenfelder definieren
With .PivotFields("Mat_Nr")
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("Datum")
.Orientation = xlRowField
.Position = 2
'keine Teilergebnisse für Feld anzeigen
.Subtotals = Array(False, False, False, False, False, _
False, False, False, False, False, False, False)
End With
With .PivotFields("Text01")
.Orientation = xlRowField
.Position = 3
End With
'Datenfelder bei mehreren Datenfeldern im Zeilenbereich anzeigen
With .DataPivotField
.Orientation = xlRowField
.Position = 4
End With
'Spaltenfelder definieren
With .PivotFields("Text02")
.Orientation = xlColumnField
.Position = 1
End With
End With
End Sub

Anzeige
AW: Erstellung Pivottabelle per Makro
01.04.2015 10:41:58
Carlu
Hallo Franz,
zunächst sehr herzlichen Dank für Deine Erläuterung und Deine sehr ausführliche Ausarbeitung!!!
Ich habe den Code heute Morgen direkt eingebaut zunächst bis zu Deinem genannten Exit Sub und parallel das Lokalfenster mitlaufen lassen, um die Werte der Objektvariablen ansehen zu können.
Dabei wird auch für rngData der korrekte Wert mit u.a. der 153 als letzte Datenzeile im Tabellenblatt Materialliste angezeigt.
Allerdings kommt an quasi gleicher Stelle (s. folgend) wie vorher die gleiche Fehlermeldung:
wkb.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:="'" & .Name & "'!" & rngData.Address(True, True, xlR1C1), _
Version:=xlPivotTableVersion14).CreatePivotTable _
Tabledestination:="'" & wksPivot.Name & "'!R3C1", _
TableName:="PivotTab1", _
Defaultversion:=xlPivotTableVersion14
Muss ich an dieser Stelle noch etwas ergänzen bzw. habe ich hier etwas vergessen abzuändern?
Um eine erneute Hilfe wäre ich natürlich erneut sehr dankbar!!!
Grüße
Carlu

Anzeige
AW: Erstellung Pivottabelle per Makro
01.04.2015 13:12:34
fcs
Hallo Carlu,
ich hab keine gute Idee, wo da bei dir der Wurm drin ist.
Ich hatte mir eine kleine Testtabelle mit 49 Spalten gebastelt und da funktioniert es reibungslos.
Ich arbeite mit der Excelversion Office Professional 2010/Excel 2010 (Version: 14.0.7128.5000 (32-Bit)) und VBA 7.0 (Version 1627, Forms3: 14.0.6009.100).
Ich hab noch ein paar Dinge probiert:
1. Parameter für die Adresse der Datenquelle xlR1C1 --&gt xlA1
Excel war da in der Vergangenheit pingelig und der Rekorder unsauber.
      wkb.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:="'" & .Name & "'!" & rngData.Address(True, True, xlA1), _
Version:=xlPivotTableVersion14).CreatePivotTable _
Tabledestination:="'" & wksPivot.Name & "'!R3C1", _
TableName:="PivotTab1", _
Defaultversion:=xlPivotTableVersion14

2. Parameter für die Version von PivotCache und PivotTabelle weglassen
      wkb.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:="'" & .Name & "'!" & rngData.Address(True, True, xlR1C1)) _
.CreatePivotTable Tabledestination:="'" & wksPivot.Name & "'!R3C1", _
TableName:="PivotTab1"

Mit beiden Änderungen funktionierte das Makro bei mir.
In der Datenquelle
- dürfen in der Titelzeile keine leeren Zellen vorkommen
- dürfen in der Titelzeile keine verbundenen Zellen vorkommen
- sollte man verbundene Zellen im Datenbereich vermeiden, sie können Probleme bei der Auswertung machen.
Das hätte aber auch schon beim manuellen Erstellen des Pivotberichts als Fehler angezeigt werden müssen.
Hier bin ich dann ohne Beispieltabelle mit meinem Excel-Latei am Ende.
Gruß
Franz
Anzeige

77 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige