How to delete the particular rows and format the pivot table using VBA

[Menaka]

Hi,

I created pivot table using vba , but its not in the format what I want can please help on this.

I've attached my excel sheet and below is the code. Please take a look at it and correct me.

Added my attachement in next post :P

<code>
Sub pivotTable()
Set wb1 = ThisWorkbook
Dim ws4 As Worksheet
Set ws4 = wb1.Worksheets("Sheet5")

Dim pt As pivotTable
Dim cacheOfpt As PivotCache 'this is the source data of pt
Dim pf As PivotField
Dim pi As PivotItem

With ws4
On Error Resume Next
Sheets("Sheet5").Select
ActiveSheet.PivotTables("Distribution of Orders across the age").TableRange2.Clear 'delete the pivot table if any exists
'set the cache of pt
Sheets("Sheet1").Select
Set cacheOfpt = ActiveWorkbook.PivotCaches.Create(xlDatabase, Range("A1:Z1031"))
'Create the pt
Sheets("Sheet5").Select
Set pt = ActiveSheet.PivotTables.Add(cacheOfpt, Range("A1"), "Distribution of Orders across the age")
'put fields in
With pt
'add the fields
.PivotFields("BUCKET").Orientation = xlRowField
.PivotFields("ROOTORDERTYPE").Orientation = xlRowField
.PivotFields("Aging").Orientation = xlColumnField
.PivotFields("ORDER_NUM").Orientation = xlDataField
'go to classic view
.RowAxisLayout xlTabularRow
End With
End With
End Sub
</code>

In sheet5 of the attached work book, 1st pivot table is what created using VB and the second table is the format in which I want.

Thanks in Advance,
Menaka B