PDA

View Full Version : [SOLVED] Pivot field moments Postion{1,5}. in case if the items availabe



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

Hudson
11-25-2016, 01:40 PM
Its error "400" i encountered .:)

p45cal
11-25-2016, 02:12 PM
Example file please

Hudson
11-25-2016, 02:32 PM
here you go ..

p45cal
11-25-2016, 05:16 PM
Res code segments is the problem? Yes?
What column do you want res code segments to be in Input Raw Data sheet?

Otherwise if it's Current and >120, you given me a file where all the items DO exist!

Try replacing:
With .PivotFields("Ageing Buckets as on today")
.Orientation = xlColumnField
.Position = 1
End With
.PivotFields("Ageing Buckets as on today").PivotItems(">120").Position = 5
with:

With .PivotFields("Ageing Buckets as on today")
.Orientation = xlColumnField
.Position = 1
i = Application.GetCustomListNum(Array("Current", "0-30", "31-60", "61-90", "90-120", ">120"))
If i = 0 Then
Application.AddCustomList Array("Current", "0-30", "31-60", "61-90", "90-120", ">120")
i = Application.GetCustomListNum(Array("Current", "0-30", "31-60", "61-90", "90-120", ">120"))
End If
.DataRange.Sort Order1:=xlAscending, Type:=xlSortLabels, OrderCustom:=i + 1, Orientation:=xlLeftToRight
End With

Hudson
11-25-2016, 08:47 PM
My mistake i have given you the incorrect file. because of file seize i had to remove some data.

Code i have shared is working fine the only problem is with the pivot field items . every time i encounter with error and had to halt macro . when ever (>12, and current ) miss the data .

so i tried this .

On error resume next.


is this correct ?.

p45cal
11-26-2016, 03:36 AM
so i tried this .

On error resume next.


is this correct ?.Not really, it'll cover all sorts of errors you don't know are happening.
You just missed out one 'End With':
Sub Reasoncode_segment_sum_f44h()
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
i = Application.GetCustomListNum(Array("Current", "0-30", "31-60", "61-90", "90-120", ">120"))
If i = 0 Then
Application.AddCustomList Array("Current", "0-30", "31-60", "61-90", "90-120", ">120")
i = Application.GetCustomListNum(Array("Current", "0-30", "31-60", "61-90", "90-120", ">120"))
End If
.DataRange.Sort Order1:=xlAscending, Type:=xlSortLabels, OrderCustom:=i + 1, Orientation:=xlLeftToRight
End With
.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

Hudson
11-26-2016, 04:07 AM
Mate - this is awesome and it is working fantastic . as always you are the best .... and don't take other wise i am calling you mate. just that i don't know your name i had to address you mate... anyways thanks for your help.