Pivot Table via VBA

Forumthread: Pivot Table via VBA

Pivot Table via VBA

12.12.2014 11:28:45

Matthias

Hi,
Hi, I wanted to create a pivot table with using VBA (since I have to adjust a few options for each colomn and that is quite annoying if the number of columns is sufficienty high ;-) ) So I recorded a Macro and did the steps manually in Excel. Below you see the first part of the code of the recorded Macro:

Sub Macro1()
'I select the data, click to insert --> Pivot table...
Range("A1:I17").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "Tabelle1!R1C1:R17C9",  _
Version:=xlPivotTableVersion14).CreatePivotTable _ TableDestination:="Tabelle1!R1C14", TableName:="PivotTable1", _ DefaultVersion:=xlPivotTableVersion14
Sheets("Tabelle1").Select
Cells(1, 14).Select
End Sub
However, I might have more or less than 17 rows (number of columns is fixed) in my table.  _
Therefore, I need a more general code. I tried the following:
Sub Macro2()
Cells(1, 1).Select
ActiveCell.CurrentRegion.Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "Tabelle1! _
CurrentRegion", Version:=xlPivotTableVersion14).CreatePivotTable _ TableDestination:="Tabelle1!R1C14", TableName:="PivotTable1", _ DefaultVersion:=xlPivotTableVersion14 Sheets("Tabelle1").Select
End Sub()
But this does not work. When I debug, the third command: "ActiveWorook.PivotCahes.Create.... _
xlPivotTableVersion14 is the problem. And since this commmand is similar to the command in the first example, i think that the problem is in the second row of that command: SourceData:=Tabelle!CurrentRegion", If you compare it to the first example,it says "Tabelle1!R1C1:R17C9" Do you have an idea what I have to insert here e.g. "Tabelle1!ActiveCell" ? Something like that? Or do I have to dim the CurrentRegion? Regards Matthias

1
Beitrag zum Forumthread

Beitrag zu diesem Forumthread

Betreff

Datum

Anwender

AW: Pivot Table via VBA

12.12.2014 15:51:47

fcs

AW: Pivot Table via VBA

12.12.2014 15:51:47

fcs

Hello Mathias,
here I have a makro for You with a more general code to generate a pivot-table.
best regards
Franz

Sub MakePivottable()
Dim wksData As Worksheet
Dim wksPivot As Worksheet, rngDestination
Dim rngData As Range
Dim pvTab As PivotTable, pvField As PivotField
Set wksData = ActiveSheet 'Worksheet with source data of pivottable
Set rngData = wksData.Cells(1, 1).CurrentRegion 'Range with source data
'set destinationtable for pivottable
'Place pivottable on active sheet
'  Set wksPivot = ActiveSheet
'  Set rngDestination = wksPivot.Range("N1")
'Place pivottable on new worksheet
Set wksPivot = ActiveWorkbook.Worksheets.Add(after:=wksData)
Set rngDestination = wksPivot.Range("A1")
wksData.Parent.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"'" & wksData.Name & "'!" & rngData.Address(True, True, xlR1C1), _
Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:=rngDestination
wksPivot.Activate
rngDestination.Select
Set pvTab = wksPivot.PivotTables(wksPivot.PivotTables.Count)
With pvTab
'Add Rowfields
Set pvField = .PivotFields("Jahr")
pvField.Orientation = xlRowField
Set pvField = .PivotFields("Land")
pvField.Orientation = xlRowField
'Add Columnfields
Set pvField = .PivotFields("Monat")
pvField.Orientation = xlColumnField
'Add Datafield
Set pvField = .PivotFields("Tilgung")
pvField.Orientation = xlDataField
'Format Datafield
Set pvField = .DataFields(.DataFields.Count)
With pvField
.Function = xlSum
.Name = "Summe Tilgung"
.NumberFormat = "#,##0"
End With
End With
End Sub

Beliebteste Forumthreads (12 Monate)

Forumthread: Pivot Table via VBA

1 Beitrag zum Forumthread Beitrag zu diesem Forumthread

Forumthreads zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

1
Beitrag zum Forumthread

Beitrag zu diesem Forumthread