Unable to create Pivot Table by VBA

[srlakra]

Hi,

I tried creating the pivot table by VBA code but I failed.

Can Anyone please help me???


Sub Pivot_Test()
Dim Psheet As Worksheet
Dim Dsheet As Worksheet
Dim Pcache As PivotCache
Dim Ptable As PivotTables
Dim Prange As Range
Dim Lastrow As Long
Dim Lastcol As Long

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotTable").Delete
Sheets.Add before:=ActiveSheet
ActiveSheet.name = "PivotTable"
Application.DisplayAlerts = True
Set Psheet = Worksheets("PivotTable")
Set Dsheet = Worksheets("Data")
Lastrow = Dsheet.Cells(Rows.Count, 1).End(xlUp).Row
Lastcol = Dsheet.Cells(1, Columns.Count).End(xlLeft).Column
Set Prange = Dsheet.Cells(1, 1).Resize(Lastrow, Lastcol)
Set Pcache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Prange).CreatePivotTable(TableDestination:=Psheet.Cells(2, 2), tablename:="DemandPivotTable")
Set Ptable = Pcache.CreatePivotTable(TableDestination:=Psheet.Cells(1, 1), tablename:="DemandPivotTable")
With ActiveSheet.PivotTables("DemandPivotTable").PivotFields("UK")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("DemandPivotTable").PivotFields("Wk 22 ~ 25")
.Orientation = xlDataField
.Position = 1
.Function = xlSum
.name = "Wk 22 ~ 25"
End With
End Sub



[TABLE="width: 584"]
<tbody>[TR]
[TD]S/L
[/TD]
[TD]Material
[/TD]
[TD]Prime
[/TD]
[TD]UK
[/TD]
[TD]201722(W)
[/TD]
[TD]201723(W)
[/TD]
[TD]201724(W)
[/TD]
[TD]201725(W)
[/TD]
[TD]Wk 22 ~ 25
[/TD]
[/TR]
[TR]
[TD]AB
[/TD]
[TD]a1
[/TD]
[TD]as
[/TD]
[TD]ABas
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD]AB
[/TD]
[TD]ae
[/TD]
[TD]ad
[/TD]
[TD]ABad
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]AB
[/TD]
[TD]fr
[/TD]
[TD]a
[/TD]
[TD]ABa
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]AB
[/TD]
[TD]hj
[/TD]
[TD]df
[/TD]
[TD]ABdf
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]AB
[/TD]
[TD]ki
[/TD]
[TD]t
[/TD]
[TD]ABt
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]7
[/TD]
[/TR]
[TR]
[TD]AB
[/TD]
[TD]lo
[/TD]
[TD]g
[/TD]
[TD]ABg
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]AB
[/TD]
[TD]pp
[/TD]
[TD]h
[/TD]
[TD]ABh
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]AB
[/TD]
[TD]dv
[/TD]
[TD]ujj
[/TD]
[TD]ABujj
[/TD]
[TD="align: right"]38
[/TD]
[TD="align: right"]304
[/TD]
[TD="align: right"]126
[/TD]
[TD="align: right"]145
[/TD]
[TD="align: right"]613
[/TD]
[/TR]
[TR]
[TD]AB
[/TD]
[TD]df
[/TD]
[TD]ff
[/TD]
[TD]ABff
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]AB
[/TD]
[TD]se
[/TD]
[TD]fty
[/TD]
[TD]ABfty
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]AB
[/TD]
[TD]ss
[/TD]
[TD]jko
[/TD]
[TD]ABjko
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[/TR]
</tbody>[/TABLE]




Regards,
Satish