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

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

Hello Menaka,
Attached, please find updated pivot table.
Best Regards/VKS

Thanks for your reply..

Can you please say what changes you have made in the code?


Thanks in Advance,
Menaka

Hello Menaka,
Click anywhere on your pivot table to activate
Right click and select pivot table options
Go to display tab & check classic pivot table layout
Click OK
Go to Row label RootOrderType and select the drop down
Uncheck the blank and you will get the pivot table in the desired format
Or
Go to your raw data and get rid of blanks there before making a pivot table
Hope this helps.
Best Regards/VKS