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:
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
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
|