Lesly
09-25-2012, 11:17 PM
Hello,
I'm just learning VBA and i got to the point that i need often pivottable's
But when you record it always take a range!
So i thought that the following will work but it doesn't :(
Can anyone have a quick look and see what i did wrong or forgot?
Thx a lot for your help
Private Sub()
Dim LR As Long
LR = ActiveSheet.UsedRange.Rows.Count
Sheets.Add
ActiveSheet.Name = "test"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Pivot!R1C1:R" & LR, Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="test!R3C1", TableName:="Draaitabel5", DefaultVersion _
:=xlPivotTableVersion14
Sheets("test").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("Draaitabel5").PivotFields("a")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("Draaitabel5").PivotFields("b")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("Draaitabel5").AddDataField ActiveSheet.PivotTables( _
"Draaitabel5").PivotFields("c"), "Som van c", xlSum
With ActiveSheet.PivotTables("Draaitabel2").PivotFields("d")
.Orientation = xlPageField
.Position = 1
End With
End Sub
I'm just learning VBA and i got to the point that i need often pivottable's
But when you record it always take a range!
So i thought that the following will work but it doesn't :(
Can anyone have a quick look and see what i did wrong or forgot?
Thx a lot for your help
Private Sub()
Dim LR As Long
LR = ActiveSheet.UsedRange.Rows.Count
Sheets.Add
ActiveSheet.Name = "test"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Pivot!R1C1:R" & LR, Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="test!R3C1", TableName:="Draaitabel5", DefaultVersion _
:=xlPivotTableVersion14
Sheets("test").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("Draaitabel5").PivotFields("a")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("Draaitabel5").PivotFields("b")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("Draaitabel5").AddDataField ActiveSheet.PivotTables( _
"Draaitabel5").PivotFields("c"), "Som van c", xlSum
With ActiveSheet.PivotTables("Draaitabel2").PivotFields("d")
.Orientation = xlPageField
.Position = 1
End With
End Sub