Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
VBA Formel für Pivot-Tabellen
11.07.2018 10:50:36
R.S.
Hallo zusammen
Ich habe ein Excel File mit vielen Daten (über 27'000 Zeilen).
Dieses File variiert von Woche zu Woche mit den Anzahl Zeilen, da immer neue Fälle dazu kommen. Daher eine Schlaufe wichtig die individuell bis zur letzten befüllten Zeile die Daten nimmt (Es hat keine leeren Zeilen dazwischen bis am Ende).
Aus diesen Daten muss ich 3 Pivot Tabellen machen. Dafür sollen 3 verschiedene Tabellenblätter am Schluss dienen: Fallstatus, Kunden und Adressstatus.
Die Daten welche für die Pivottabelle benötigen werden sind:
A (Fall Nr)
G (akt. Kundenname)
J (eff. Gläubigername)
W (Forderungssumme)
AA (akt. Fallstatus Name)
AI (akt. Adressstatus (20 & Beschreibungstext))
AJ (Zahlungen total)
Pivottabelle Fallstatus sollte wir folgt aussehen:
Zeilenbeschriftung: 1. akt. Fallstatus 2. akt. Kundenname 3. eff. Gläubigername
Werte: 1. Anzahl von Fall Nr 2. Summe von Forderungssumme 3. Summe von Zahlungen Total
Pivottabelle Kunden:
Zeilenbeschriftung: 1. akt. Kundenname 2. eff. Gläubigername 3. akt. Fallstatus Name
Werte: 1. Anzahl von Fall Nr 2. Summe von Forderungssumme 3. Summe von Zahlungen Total
Pivottabelle Adresstatus:
Zeilenbeschriftung: 1. akt. Adressstatus (20 & Beschreibungstext) 2. akt. Kundenname 3. eff. Gläubigername 4. akt. Fallstatus Name
Werte: 1. Anzahl von Fall Nr 2. Summe von Forderungssumme 3. Summe von Zahlungen Total
Am Schluss müsste mann dann noch bei den Zahlen die Dezimalstelle auf 0 stellen und den 1000er Trennstrich aktivieren.
Kann mir hier jemand helfen?

5
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VBA Formel für Pivot-Tabellen
11.07.2018 15:36:18
Rob
Und was willst Du genau wissen? Wie man den Cache einer Pivot in VBA erstellt oder wie man Fields hinzufügt oder Slicer? Ich glaube nicht, dass jemand bereit ist das komplette Makro für Dich zu schreiben. Ich würde mich an Deiner Stelle selber daran versuchen und wenn Du nicht weiterkommst, kannst Du Deinen Code posten. Dann hast Du garantiert schneller eine Antwort als Du schauen kannst.
AW: VBA Formel für Pivot-Tabellen
11.07.2018 15:41:57
Rob
Zur Hilfestellung ein Makro das ich zur Erstellung einer Pivot verwende:

Sub CreatPivot()
Dim pc As PivotCache
Dim pt As PivotTable
Dim sc, sc1, sc2 As SlicerCache
Dim sl, sl1, sl2 As Slicer
Dim rngPivotTableSource, rngPivotTableDest As Range
Dim ws As Worksheet
Dim slPosition As Range
Workbooks("Makro_Materiallisten.xlsm").Activate
Sheets("Pivot").Activate
Set rngPivotTableSource = Sheets("Zusammenfassung").Range("A1").CurrentRegion
Set rngPivotTableDest = Sheets("Pivot").Range("A3")
Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, rngPivotTableSource, Version:= _
xlPivotTableVersion15)
Set pt = pc.CreatePivotTable(rngPivotTableDest, TableName:="PivotPartslists")
pt.AddFields RowFields:=Array("Bestellnummer", "Bezeichnung", "Bezeichnung2", "Material", " _
Kategorie"), PageFields:="Project no"
pt.AddDataField pt.PivotFields("Menge")
'Für Klassisches PivotTableLayout
With ActiveSheet.PivotTables("PivotPartslists")
.InGridDropZones = True
.RowAxisLayout xlTabularRow
End With
Set sc = ActiveWorkbook.SlicerCaches.Add2(pt, "Kategorie")
Set sl = sc.Slicers.Add(Sheets("Pivot"))
Set sc1 = ActiveWorkbook.SlicerCaches.Add2(pt, "Part no")
Set sl1 = sc1.Slicers.Add(Sheets("Pivot"))
'Positionierung der Slicer
Set slPosition = pt.TableRange1
sl.Top = slPosition.Top
sl.Left = slPosition.Left + slPosition.Width + 20
sl1.Top = slPosition.Top
sl1.Left = slPosition.Left + slPosition.Width + 180
With Sheets("Pivot")
.Columns("A:E").Autofit
End With
End Sub

Anzeige
AW: VBA Formel für Pivot-Tabellen
12.07.2018 15:44:19
R.S.
Hi Rob
Habe nun alles in einem Exceldokument zusammen. Folgende 2 Fragen hab ich noch:
Wie kann ich gewährleisten, dass jeweils alle befüllten Zeilen genommen werden, da von Woche zu Woche mehr Zeilen hinzukommen?
Am Schluss wäre super, wenn das ganze Dokument sich kopieren würde, und als separates abspeichern und im Original alles gelöscht wäre, damit nächste Woche wieder bei 0 angefangen werden kann.
Sheets("Fällebestand").Activate
Rows("1:1").Select
Selection.AutoFilter
Range("C9").Select
' Pivot Kunden
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Fällebestand!R1C1:R27921C40", Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="Kunden!R3C1", TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion14
Sheets("Kunden").Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields("akt. Kundenname")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("eff. Gläubigername")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("akt. Fallstatus Name")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Fall Nr"), "Anzahl von Fall Nr", xlCount
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Forderungssumme"), " Summe von Forderungssumme", _
xlSum
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Zahlungen total"), "Summe von Zahlungen total", _
xlSum
ActiveSheet.PivotTables("PivotTable2").PivotFields("akt. Kundenname").PivotItems("-").Visible = False
Range("A4").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("akt. Kundenname"). _
ShowDetail = False
ActiveSheet.Range("B4:D22").Select
ActiveSheet.Range("D22").Activate
Selection.NumberFormat = "#,##0"
Sheets("Fällebestand").Select
' Pivot Fallstatus
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Fällebestand!R1C1:R27921C40", Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="Fallstatus!R3C1", TableName:= _
"PivotTable3", DefaultVersion:=xlPivotTableVersion14
Sheets("Fallstatus").Select
With ActiveSheet.PivotTables("PivotTable3").PivotFields("akt. Fallstatus Name")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("akt. Kundenname")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("eff. Gläubigername")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Fall Nr"), "Anzahl von Fall Nr", xlCount
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Forderungssumme"), " Summe von Forderungssumme", _
xlSum
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Zahlungen total"), "Summe von Zahlungen total", _
xlSum
With ActiveSheet.PivotTables("PivotTable3").PivotFields("akt. Fallstatus Name")
.PivotItems("-").Visible = False
End With
ActiveSheet.PivotTables("PivotTable3").PivotSelect _
"'akt. Fallstatus Name'[All]", xlLabelOnly + xlFirstRow, True
Range("A4").Select
ActiveSheet.PivotTables("PivotTable3").PivotFields("akt. Fallstatus Name"). _
ShowDetail = False
Range("B4:D25").Select
Range("D25").Activate
Selection.NumberFormat = "#,##0"
Sheets("Fällebestand").Select
'Pivot Adressstauts
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Fällebestand!R1C1:R27921C40", Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="Adressstatus!R3C1", TableName:= _
"PivotTable4", DefaultVersion:=xlPivotTableVersion14
Sheets("Adressstatus").Select
With ActiveSheet.PivotTables("PivotTable4").PivotFields( _
"akt. Adressstatus (20 & Beschreibungstext)")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields("akt. Kundenname")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields("eff. Gläubigername")
.Orientation = xlRowField
.Position = 3
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields("akt. Fallstatus Name")
.Orientation = xlRowField
.Position = 4
End With
ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
"PivotTable4").PivotFields("Fall Nr"), "Anzahl von Fall Nr", xlCount
ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
"PivotTable4").PivotFields("Forderungssumme"), " Summe von Forderungssumme", _
xlSum
ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
"PivotTable4").PivotFields("Zahlungen total"), "Summe von Zahlungen total", _
xlSum
With ActiveSheet.PivotTables("PivotTable4").PivotFields( _
"akt. Adressstatus (20 & Beschreibungstext)")
.PivotItems(" ").Visible = False
End With
Range("A4").Select
ActiveSheet.PivotTables("PivotTable4").PivotFields( _
"akt. Adressstatus (20 & Beschreibungstext)").ShowDetail = False
Range("B4:D14").Select
Range("D14").Activate
Selection.NumberFormat = "#,##0"
End Sub
Anzeige
AW: VBA Formel für Pivot-Tabellen
13.07.2018 08:56:57
Rob
Hi,
durch die folgende Zeile ist die Dynamik bereits eingebaut (CurrentRegion):
Set rngPivotTableSource = Sheets("Zusammenfassung").Range("A1").CurrentRegion
Damit wird gewährleistet, dass er alle Zeilen für die Pivot berücksichtigt, auch wenn neue hinzukommen. Du musst die Pivot natürlich aktualisieren, wenn Du Zeilen hinzufügst.
Zu Deiner zweiten Frage: SaveAs-Methode für die Kopie und anschließend kannst Du das alte Workbook löschen. Ich denke hierzu gibt es genügend Beispiele im Internet.
AW: VBA Formel für Pivot-Tabellen
13.07.2018 13:27:02
R.S.
Hi Rob,
Woe genau sollte ich den folgenden Befehl einfügen?
Set rngPivotTableSource = Sheets("Zusammenfassung").Range("A1").CurrentRegion
Anzeige

310 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige