Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema Label
BildScreenshot zu Label Label-Seite mit Beispielarbeitsmappe aufrufen

VBA Formel für Pivot-Tabellen


Betrifft: VBA Formel für Pivot-Tabellen von: R.S.
Geschrieben am: 11.07.2018 10:50:36

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?

  

Betrifft: AW: VBA Formel für Pivot-Tabellen von: Rob
Geschrieben am: 11.07.2018 15:36:18

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.


  

Betrifft: AW: VBA Formel für Pivot-Tabellen von: Rob
Geschrieben am: 11.07.2018 15:41:57

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



  

Betrifft: AW: VBA Formel für Pivot-Tabellen von: R.S.
Geschrieben am: 12.07.2018 15:44:19

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


  

Betrifft: AW: VBA Formel für Pivot-Tabellen von: Rob
Geschrieben am: 13.07.2018 08:56:57

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.


  

Betrifft: AW: VBA Formel für Pivot-Tabellen von: R.S.
Geschrieben am: 13.07.2018 13:27:02

Hi Rob,

Woe genau sollte ich den folgenden Befehl einfügen?

Set rngPivotTableSource = Sheets("Zusammenfassung").Range("A1").CurrentRegion


Beiträge aus dem Excel-Forum zum Thema "VBA Formel für Pivot-Tabellen"