Pivot Table via VBA
12.12.2014 11:28:45
Matthias
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