PDA

View Full Version : Pivot Table VBA



Jenst
09-23-2016, 03:56 AM
Hi guys,
I want to create a pivot table without directly pointing out the names of the Pivot Fields, instead I want to refer to the cell value.
Has anyone an idea how to do it??

Range("A1:BQ22").Select
Range("J8").Activate
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
Sheets("DATA").Range("A1").CurrentRegion, Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet2!R20C1", TableName:="PivotTable2", DefaultVersion _
:=xlPivotTableVersion14

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(Cells(2, 28).Value), "Average" & Cells(2, 28).Value, xlAverage

:thumb
Thanks for your help!
Step

mana
09-23-2016, 04:41 AM
Option Explicit

Sub test()
Dim pvc As PivotCache
Dim pvt As PivotTable
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set ws1 = Worksheets("DATA")
Set ws2 = Worksheets("Sheet2")

Set pvc = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=ws1.Range("A1").CurrentRegion)

Set pvt = pvc.CreatePivotTable(TableDestination:=ws2.Range("A20"))


With pvt
.AddDataField Field:=.PivotFields(ws1.Cells(1, 4).Value), _
Caption:="Average" & ws1.Cells(1, 4).Value, _
Function:=xlAverage
End With

End Sub

Jenst
09-24-2016, 03:54 PM
sb

Jenst
09-24-2016, 03:56 PM
Thanks for this code looks reasonable! But I get a type error for the lines set pvc=ActiveWorkbook.PivotCaches.Create etc
... can anyone explain why?? Thanks, Jens

mana
09-24-2016, 05:41 PM
Please try once more with my sample file.

17157

Jenst
09-24-2016, 05:45 PM
Is Working now, my code for transforming into a table was not correct, Thanks mana for your help. Really appreciate it! :hi: