i want to fill empty cells of pivot table

October 13, 2010 at 00:24:11
Specs: Windows XP
below is the piece of code which creates a pivot table from excel data. i want to insert 0 in empty cells in pivot table. Please suggest how can i do this.

Sub OFRReport()
Dim c As Range
Dim cantCardServ As Integer
Dim cantCardServPlus As Integer
Dim cantTS, cantTSPlus As Integer
Dim cantRows, cantCols As Integer
Dim cont As Integer
Dim cardSolTotal, granTotal As Integer

' Select the information to create the pivot table
' Create the pivot table
Set c = Cells.Find(What:="SOURCE", after:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If Not c Is Nothing Then
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Data!R" + Trim(Str(c.Row)) + "C" + Trim(Str(c.Column)) + ":R10000C" + Trim(Str(c.Column + 4))).CreatePivotTable TableDestination:="", TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="SOURCE", _
ColumnFields:="STATUS", PageFields:="ACT/PASSIVE"
With ActiveSheet.PivotTables("PivotTable2").PivotFields("NO.OF RECS")
.Orientation = xlDataField
.Caption = "Sum of NO.OF RECS"
.Function = xlSum
End With
ActiveWorkbook.ShowPivotTableFieldList = True
End If

See More: i want to fill empty cells of pivot table

October 13, 2010 at 08:17:25
this isnt the programing forum you need to post this question in the programing forums


Report •
Related Solutions

Ask Question