PDA

View Full Version : [SOLVED] If only blanks PIVOT should not be inserted



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

Hudson
12-02-2016, 11:53 PM
attached is the file for your reference.

mana
12-03-2016, 05:04 AM
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
12-03-2016, 05:28 AM
Hi manna.

Thanks for your inputs . but unfortunately its nothing happening .

mana
12-03-2016, 05:44 AM
You must insert my code to your original code.

Hudson
12-03-2016, 12:03 PM
I am sorry , i am new bee to VBA . where exactly you want me to the code ..

Hudson
12-03-2016, 12:25 PM
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
12-03-2016, 05:44 PM
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
12-03-2016, 08:32 PM
Mate this is fantastic . code is working fine .. Sorry to trouble you extra ..... thanks for your help .

Hudson
12-16-2016, 02:00 PM
Mate - can you advice me under what scenarios above code would say error " Type mismatch".

mana
12-17-2016, 12:28 AM
>error " Type mismatch"

Which line of the code?

Hudson
12-17-2016, 07:19 AM
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
12-17-2016, 09:53 PM
I want to know the highligted line of VBA code.

Hudson
12-19-2016, 07:59 AM
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
12-20-2016, 04:32 AM
Hi Mana.. sorry to disturb you mate did you get the chance to look into it ?.

mana
12-20-2016, 06:11 AM
> SourceData:=Sheets("Input Raw data").Range("A1").CurrentRegion


try1

SourceData:=tbl


try2

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

Hudson
12-20-2016, 08:13 AM
No mate . I am getting compiler error .. I tried

Aussiebear
12-21-2016, 06:09 AM
Which is highlighted in yellow? This is the line that excel thinks is in error?

Hudson
12-21-2016, 07:18 AM
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
12-21-2016, 08:36 AM
when I say huge it is 90k lines ... I cant even share that data .

mana
12-22-2016, 04:43 AM
try1

With ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=tbl).CreatePivotTable(TableDestination:=Sheets("Summary").Range("F6"))


try2

With ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=tbl.Address(ReferenceStyle:=xlR1C1, External:=True)).CreatePivotTable(TableDestination:=Sheets("Summary").Range("F6"))

Hudson
01-09-2017, 08:08 AM
Mana. this is fantastic approach , I tried and indeed it worked .. thanks mate I really appreciate your efforts in this ..:)