Hudson
12-02-2016, 11:43 PM
Hi all,
I have code that put pivot in summary sheet from the source data (in put raw data ) everything is fine sometimes due to non availability of " ("Res code segments") pivot capturing blank only . basically code is designed to capture every reason code segment followed by uncheck the blank field .
Now my question is can we tweak below code to be more dynamic such that every time when there is non availability of (("Res code segments")
code should not get executed .
Sub Reasoncode_segment_Count_F40()
With ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Sheets("Input Raw data").Range("A1").CurrentRegion).CreatePivotTable(TableDestination:=Sheets("Summary").Range("F40"))
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", "91-120", ">120"))
If i = 0 Then
Application.AddCustomList Array("Current", "0-30", "31-60", "61-90", "91-120", ">120")
i = Application.GetCustomListNum(Array("Current", "0-30", "31-60", "61-90", "91-120", ">120"))
End If
.DataRange.Sort Order1:=xlAscending, Type:=xlSortLabels, OrderCustom:=i + 1, Orientation:=xlLeftToRight
End With
.AddDataField .PivotFields("Amount in doc. curr."), "Sum of Amount in doc. curr.", xlSum
'below statment make sort Grand total column sort'
With .PivotFields("Res code Segments")
.AutoSort Order:=xlDescending, Field:="Sum of Amount in doc. curr."
End With
'below code uncheck blank fields'
With .PivotFields("Res code segments")
.PivotItems("(blank)").Visible = False
End With
With .PivotFields("Sum of Amount in doc. curr.")
.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
End With
.DisplayFieldCaptions = False
.TableStyle2 = "PivotStyleMedium5"
End With
End Sub
I have code that put pivot in summary sheet from the source data (in put raw data ) everything is fine sometimes due to non availability of " ("Res code segments") pivot capturing blank only . basically code is designed to capture every reason code segment followed by uncheck the blank field .
Now my question is can we tweak below code to be more dynamic such that every time when there is non availability of (("Res code segments")
code should not get executed .
Sub Reasoncode_segment_Count_F40()
With ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Sheets("Input Raw data").Range("A1").CurrentRegion).CreatePivotTable(TableDestination:=Sheets("Summary").Range("F40"))
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", "91-120", ">120"))
If i = 0 Then
Application.AddCustomList Array("Current", "0-30", "31-60", "61-90", "91-120", ">120")
i = Application.GetCustomListNum(Array("Current", "0-30", "31-60", "61-90", "91-120", ">120"))
End If
.DataRange.Sort Order1:=xlAscending, Type:=xlSortLabels, OrderCustom:=i + 1, Orientation:=xlLeftToRight
End With
.AddDataField .PivotFields("Amount in doc. curr."), "Sum of Amount in doc. curr.", xlSum
'below statment make sort Grand total column sort'
With .PivotFields("Res code Segments")
.AutoSort Order:=xlDescending, Field:="Sum of Amount in doc. curr."
End With
'below code uncheck blank fields'
With .PivotFields("Res code segments")
.PivotItems("(blank)").Visible = False
End With
With .PivotFields("Sum of Amount in doc. curr.")
.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
End With
.DisplayFieldCaptions = False
.TableStyle2 = "PivotStyleMedium5"
End With
End Sub