Pivot Table VBA

[Jenst]

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


Thanks for your help!
Step

[mana]

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]

sb

[Jenst]

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]

Please try once more with my sample file.

20160925VBA_EXPRESS.xlsx

[Jenst]

Is Working now, my code for transforming into a table was not correct, Thanks mana for your help. Really appreciate it!