If only blanks PIVOT should not be inserted

[Hudson]

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

[Hudson]

attached is the file for your reference.

[mana]

Sub test()
    Dim tbl As Range
    
    Set tbl = Sheets("Input Raw data").Range("A1").CurrentRegion
    If WorksheetFunction.CountA(tbl.Columns("F")) = 1 Then
        MsgBox "blank only"
        Exit Sub
    End If
    
End Sub

[Hudson]

Hi manna.

Thanks for your inputs . but unfortunately its nothing happening .

[mana]

You must insert my code to your original code.

[Hudson]

I am sorry , i am new bee to VBA . where exactly you want me to the code ..

[Hudson]

I tried even that . i associated your code with mine . nothing happening .

Sub Reasoncode_segment_Count_F40()Dim tbl As Range
Set tbl = Sheets("Input Raw data").Range("A1").CurrentRegion
    
    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
    
    If WorksheetFunction.CountA(tbl.Columns("F")) = 1 Then
        MsgBox "blank only"
        Exit Sub
    End If
End Sub

is this how you wnat me to add the it ?.

[mana]

Sub Reasoncode_segment_Count_F40()
    Dim tbl As Range
     
    Set tbl = Sheets("Input Raw data").Range("A1").CurrentRegion
    If WorksheetFunction.CountA(tbl.Columns("F")) = 1 Then
        MsgBox "blank only"
        Exit Sub
    End If
    
    With ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=tbl).CreatePivotTable(TableDestination:=Sheets("Summary").Range("F40"))
        With .PivotFields("Res code Segments")
            .Orientation = xlRowField
            .Position = 1
        End With

[Hudson]

Mate this is fantastic . code is working fine .. Sorry to trouble you extra ..... thanks for your help .

[Hudson]

Mate - can you advice me under what scenarios above code would say error " Type mismatch".

[mana]

>error " Type mismatch"

Which line of the code?

[Hudson]

No certain line but when I execute code it is giving me this error...entire code I would say ... is this because of huge data ? .. can that be possible? .. I mean when I was trying to put pivot using this code more than 80000 lines so is this because of that?

[mana]

I want to know the highligted line of VBA code.

[Hudson]

Hi mana.

It is basically in the first line . I tried step by step using F8 below is the line it is causing .

With ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Sheets("Input Raw data").Range("A1").CurrentRegion).CreatePivotTable(TableDestination:=Sheets("Summary").Range("F6"))
        With .PivotFields("Classification")
            .Orientation = xlRowField
            .Position = 1

[Hudson]

Hi Mana.. sorry to disturb you mate did you get the chance to look into it ?.

[mana]

> SourceData:=Sheets("Input Raw data").Range("A1").CurrentRegion

try1

SourceData:=tbl

try2

SourceData:=tbl.Address(ReferenceStyle:=xlR1C1, External:=True)

[Hudson]

No mate . I am getting compiler error .. I tried

[Aussiebear]

Which is highlighted in yellow? This is the line that excel thinks is in error?

[Hudson]

Hi Aussiebear,

First of all thanks for addressing my issue .

Yes . if you want to see entire code I would give you .. and the same code is working if the data is less . but I am getting error " Type miss match " when the data is huge . so could this be the reason ?.

[Hudson]

when I say huge it is 90k lines ... I cant even share that data .