Hello Guru's,

Trying to create a new pivototabel in new sheet but it is showing just the fields without any data in it. i tried lots of codes like refreshall, refreshtable but nothing is working if i check datasource it is already there and correct, everytime user have to manually refresh pivottable by right clicking on it to get the data. and i used some codes to get rid of subtotals but that is also not working it just removes the subtotal of first field which is mentioned in that line of code. below is my attempt. any help would be appreciated.

Sub pvttable()
Dim ptdata As Range, pt As pivottable, ptcache As PivotCache, wsp As Worksheet

Workbooks("EXW").Sheets.Add.Name = "summary"
Set wsp = Workbooks("EXW").Sheets("summary")
Set ptdata = Workbooks("EXW").Sheets("Data").Range("A1").CurrentRegion
Set ptcache = Workbooks("EXW").PivotCaches.Add(xlDatabase, ptdata)
Set pt = ptcache.CreatePivotTable(wsp.Cells(1, 1))
With pt
    .ManualUpdate = True
    .AddFields RowFields:=(Array("reference#", "Name", "Type", "input"))
    .PivotFields("psn").Orientation = xlDataField
    .PivotFields("reference#").Subtotals(1) = True
    .PivotFields("reference#").Subtotals(1) = False
    .InGridDropZones = True
    .RowAxisLayout xlTabularRow
    .TableStyle2 = ""
    .DisplayContextTooltips = False
    .ShowDrillIndicators = False
    .ColumnGrand = False
    .RowGrand = False
    .ManualUpdate = False
    .ManualUpdate = True
End With
Workbooks("EXW").RefreshAll
End Sub