Hudson
11-25-2016, 01:24 PM
Hi p45cal ,
It was a great help from you in my earlier post in relation to pivot construction . in the below link .unfortunately i was not able to open the link (404 error).
and i was able to tweak more with your help . below is the code for your reference. in the below code i moved few fields to right and beginning (">120") and ("Current") not necessarily we get to see this fielditems in every data i run. below code only runs if the field items exist . if no fileds macro giving me error message. can you help me please .
Sub Reasoncode_segment_sum_f44() With ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Sheets("Input Raw data").Range("A1").CurrentRegion).CreatePivotTable(TableDestination:=Sheets("Summary").Range("F44"))
With .PivotFields("Res code Segments")
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("Ageing Buckets as on today")
.Orientation = xlColumnField
.Position = 1
End With
.PivotFields("Ageing Buckets as on today").PivotItems(">120").Position = 5
.AddDataField .PivotFields("Amount in doc. curr."), "Count of Amount in doc. curr.", xlCount
'below statment make sort Grand total column sort'
With .PivotFields("Res code Segments")
.AutoSort Order:=xlDescending, field:="Count of Amount in doc. curr."
End With
'below code uncheck blank fields'
With .PivotFields("Res code segments")
.PivotItems("(blank)").Visible = False
End With
.DisplayFieldCaptions = False
.TableStyle2 = "PivotStyleMedium4"
End With
End Sub
It was a great help from you in my earlier post in relation to pivot construction . in the below link .unfortunately i was not able to open the link (404 error).
and i was able to tweak more with your help . below is the code for your reference. in the below code i moved few fields to right and beginning (">120") and ("Current") not necessarily we get to see this fielditems in every data i run. below code only runs if the field items exist . if no fileds macro giving me error message. can you help me please .
Sub Reasoncode_segment_sum_f44() With ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Sheets("Input Raw data").Range("A1").CurrentRegion).CreatePivotTable(TableDestination:=Sheets("Summary").Range("F44"))
With .PivotFields("Res code Segments")
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("Ageing Buckets as on today")
.Orientation = xlColumnField
.Position = 1
End With
.PivotFields("Ageing Buckets as on today").PivotItems(">120").Position = 5
.AddDataField .PivotFields("Amount in doc. curr."), "Count of Amount in doc. curr.", xlCount
'below statment make sort Grand total column sort'
With .PivotFields("Res code Segments")
.AutoSort Order:=xlDescending, field:="Count of Amount in doc. curr."
End With
'below code uncheck blank fields'
With .PivotFields("Res code segments")
.PivotItems("(blank)").Visible = False
End With
.DisplayFieldCaptions = False
.TableStyle2 = "PivotStyleMedium4"
End With
End Sub