Hi Aflatoon,
I have tried some more to modify the code, but i am getting this error while calling it. Can you help me to complete it.
08-Oct-13 09-04-01.png
Calling the sub
Call MakePivotCriteria1(PiSheet, "G1", "OPEN_CALLS", Array("SSP NAME"), Array(), Array("SR No.", "Task Desc"), Array("Bus Stream"), Array(True, True))
Sub for making pivot
Sub MakePivotCriteria1(DestSheetName As String, PlacementCell As String, SourceTableName As String, _
RowFields, ColFields, DataFields, PageFields, ArithOps)
Dim n As Long
Dim pt As PivotTable
Dim vRows, vCols, vData, vPages
'------------------------------
Set pt = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SourceTableName).CreatePivotTable( _
TableDestination:=Worksheets(DestSheetName).Range(PlacementCell), TableName:=SourceTableName)
'------------------------------------------------------------------------------------------------------------------
With pt
.ManualUpdate = True
'----------------------
If IsArray(RowFields) Then
vRows = RowFields
Else
vRows = Array(RowFields)
End If
'----------------------
For n = LBound(vRows) To UBound(vRows)
With .PivotFields(vRows(n))
.Orientation = xlRowField
.Position = n + 1
End With
Next n
'--------------------------
If IsMissing(ColFields) Then
GoTo Action1
End If
'--------------------------
If IsArray(ColFields) Then
vCols = ColFields
Else
vCols = Array(ColFields)
End If
'-------------------------------------
For n = LBound(vCols) To UBound(vCols)
With .PivotFields(vCols(n))
.Orientation = xlColumnField
.Position = n + 1
End With
Next n
'--------------------------
Action1: If IsArray(DataFields) Then
vData = DataFields
Else
vData = Array(DataFields)
End If
'-------------------------------------
For n = LBound(vData) To UBound(vData)
With .PivotFields(vData(n))
.Orientation = xlDataField
.Position = n + 1
If ArithOps = True Then
.Function = xlCount
Else
.Function = xlSum
End If
.NumberFormat = "0"
End With
Next n
'--------------------------
Check2: If IsMissing(PageFields) Then
GoTo Action2
End If
If IsArray(PageFields) Then
vPages = PageFields
Else
vPages = Array(PageFields)
End If
'----------------------
For n = LBound(vPages) To UBound(vPages)
With .PivotFields(vPages(n))
.Orientation = xlPageField
.Position = n + 1
.EnableMultiplePageItems = True
End With
Next n
'-------------------------------------
Action2: .TableStyle2 = "PivotStyleDark5"
.ManualUpdate = False
End With
End Sub