Pivot Table Creation with Optional Arguments

Hi,

I need support in creating pivot with a code base, below is the code that i am using to create pivot. It works perfectly as expected for one parameter for row field or column field etc. But now i need to create a pivot with multiple row fields and column fields and data fields.

If anyone can support on how to pass arguments for multiple values for one parameter, it would be of great help

Thanks
Raj :)

Code:

---

Sub MakePivotCriteria(SourceSheetName As String, SourceTableName As String, RowField As String, ColField As String, DataField As String, PageField As String)
   
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SourceTableName).CreatePivotTable _
        TableDestination:=Worksheets(SourceSheetName).Range("A1"), TableName:=SourceTableName
                                                With ActiveSheet.PivotTables(SourceTableName).PivotFields(ColField)
                                                    .Orientation = xlColumnField
                                                    .Position = 1
                                                End With
                                                With ActiveSheet.PivotTables(SourceTableName).PivotFields(RowField)
                                                    .Orientation = xlRowField
                                                    .Position = 1
                                                End With
                                                With ActiveSheet.PivotTables(SourceTableName).PivotFields(DataField)
                                                    .Orientation = xlDataField
                                                    .Position = 1
                                                    .Function = xlCount
                                                    .NumberFormat = "0"
                                                End With
                                                With ActiveSheet.PivotTables(SourceTableName).PivotFields(PageField)
                                                    .Orientation = xlPageField
                                                    .Position = 1
                                                    .EnableMultiplePageItems = True
                                                End With
                                                With Worksheets(SourceSheetName).PivotTables(SourceTableName)
                                                    .TableStyle2 = "PivotStyleDark5"
                                                End With
                                               
End Sub

---

Code:

---

    With ActiveSheet.PivotTables("SamplePivotTable").PivotFields("HEADER01")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("SamplePivotTable").PivotFields("HEADER02")
        .Orientation = xlRowField
        .Position = 2                          ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    End With
    With ActiveSheet.PivotTables("SamplePivotTable").PivotFields("HEADER03")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("SamplePivotTable").AddDataField ActiveSheet. _
        PivotTables("SamplePivotTable").PivotFields("HEADER04"), "Sum of HEADER04", _
        xlSum
    ActiveSheet.PivotTables("SamplePivotTable").AddDataField ActiveSheet. _
        PivotTables("SamplePivotTable").PivotFields("HEADER05"), "Sum of HEADER05", _
        xlSum
    With ActiveSheet.PivotTables("SamplePivotTable").DataPivotField
        .Orientation = xlColumnField
        .Position = 2
    End With

---

Probably just need to use .Position = 2, = 3, etc.

Paul

You can pass arrays of the names of the fields:
[vba]Sub MakePivotCriteria(SourceSheetName As String, SourceTableName As String, RowFields() As String, _
ColFields() As String, DataFields() As String, PageFields() As String)

Dim n As Long
Dim pt As PivotTable

Set pt = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SourceTableName).CreatePivotTable( _
TableDestination:=Worksheets(SourceSheetName).Range("A1"), TableName:=SourceTableName)

With pt
For n = LBound(ColFields) To UBound(ColFields)
With .PivotFields(ColFields(n))
.Orientation = xlColumnField
.Position = n + 1
End With
Next n
For n = LBound(RowFields) To UBound(RowFields)
With .PivotFields(RowFields(n))
.Orientation = xlRowField
.Position = n + 1
End With
Next n
For n = LBound(DataFields) To UBound(DataFields)
With .PivotFields(DataFields(n))
.Orientation = xlDataField
.Position = n + 1
.Function = xlCount
.NumberFormat = "0"
End With
Next n
For n = LBound(PageFields) To UBound(PageFields)
With .PivotFields(PageFields(n))
.Orientation = xlPageField
.Position = n + 1
.EnableMultiplePageItems = True
End With
Next n
.TableStyle2 = "PivotStyleDark5"
End With

End Sub

[/vba]

Perhaps better - this will allow you to pass either an array or a single value as the data for any or all of the fields:
[vba]
Sub MakePivotCriteria(SourceSheetName As String, SourceTableName As String, RowFields, _
ColFields, DataFields, PageFields)

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(SourceSheetName).Range("A1"), TableName:=SourceTableName)

With pt

.ManualUpdate = True

If IsArray(ColFields) Then
vCols = ColFields
Else
vCols = Array(ColFields)
End If
If IsArray(RowFields) Then
vRows = RowFields
Else
vRows = Array(RowFields)
End If

If IsArray(DataFields) Then
vData = DataFields
Else
vData = Array(DataFields)
End If

If IsArray(PageFields) Then
vPages = PageFields
Else
vPages = Array(PageFields)
End If

For n = LBound(vCols) To UBound(vCols)
With .PivotFields(vCols(n))
.Orientation = xlColumnField
.Position = n + 1
End With
Next n
For n = LBound(vRows) To UBound(vRows)
With .PivotFields(vRows(n))
.Orientation = xlRowField
.Position = n + 1
End With
Next n

For n = LBound(vData) To UBound(vData)
With .PivotFields(vData(n))
.Orientation = xlDataField
.Position = n + 1
.Function = xlCount
.NumberFormat = "0"
End With
Next n

For n = LBound(vPages) To UBound(vPages)
With .PivotFields(vPages(n))
.Orientation = xlPageField
.Position = n + 1
.EnableMultiplePageItems = True
End With
Next n

.TableStyle2 = "PivotStyleDark5"

.ManualUpdate = False
End With

End Sub
[/vba]

Hi Aflatoon / Paul,

Many thanks, for your reply. Works perfect as needed. I needed one more small change as below, will it be possible to accommodate the same.

Code:

---

Sub MakePivotCriteriaCount(SourceSheetName As String, SourceTableName As String, RowField As String, _
ColField As String, DataField As String, PageField As String, ArithOps As Boolean)
  With ActiveSheet.PivotTables(SourceTableName).PivotFields(DataField)
  .Orientation = xlDataField
  .Position = 1
  If ArithOps = True Then
  .Function = xlCount
    .Function = xlSum
    End If
  .NumberFormat = "0"
 End With

---

Raj

Yes - do give it a try and let us know if you have any problems. ;)

I think you need to pick one or the other. As it is, it will always be xlSum. Also it will only apply to the first data field, which may or may not be what you want

Code:

---

    .Orientation = xlDataField
        .Position = 1

        If ArithOps = True Then
            .Function = xlCount
            .Function = xlSum
        End If


---

Paul

Quote:

---

Originally Posted by Aflatoon

Yes - do give it a try and let us know if you have any problems. ;)

---

I think my question was not clear, i was asking how to declare .Function parameter for each DataField. may be some one will be sum and another will count. I am not clear on how to pass these within array values.

And suppose if situation is i do not have column field but have one row field and two data field. How to skip over the column field argument.

I have these queries, can you help?

Code:

---

Call MakePivotCriteria1(PiSheet, "A1", "OPEN_CALLS", "SSP NAME", , ("SR No." "Units Used"), "Bus Stream") '<=== is it correct?
   
' below is one of the situations as example:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "OPEN_CALLS").CreatePivotTable _
        TableDestination:=Worksheets(PiSheet).Range("G1"), TableName:="OPEN_CALLS1"
                                                With ActiveSheet.PivotTables("OPEN_CALLS1").PivotFields("SSP NAME")
                                                    .Orientation = xlRowField
                                                    .Position = 1
                                                End With
                                                With ActiveSheet.PivotTables("OPEN_CALLS1").PivotFields("SR No.")
                                                    .Orientation = xlDataField
                                                    .Position = 1
                                                    .Function = xlCount
                                                    .NumberFormat = "0"
                                                End With
                                                With ActiveSheet.PivotTables("OPEN_CALLS1").PivotFields("Units Used")
                                                    .Orientation = xlDataField
                                                    .Position = 2
                                                    .Function = xlSum
                                                    .NumberFormat = "0"
                                                End With
                                                With ActiveSheet.PivotTables("OPEN_CALLS1").PivotFields("Bus Stream")
                                                    .Orientation = xlPageField
                                                    .Position = 1
                                                    .EnableMultiplePageItems = True
                                                End With
                                                With Worksheets(PiSheet).PivotTables("OPEN_CALLS1")
                                                                .TableStyle2 = "PivotStyleDark5"
                                                End With

---

Raj

Quote:

---

Originally Posted by rajkumar

I think my question was not clear

---

I think you're right - perhaps because it appears to be ever-changing.

In all honesty, by the time you have made this code completely generic, it will be easier to simply write the code to create the pivot table you want each time, than it will to write the code to call this routine.

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.
Attachment 10670
Calling the sub

Code:

---

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

Code:

---

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

---

Try this:
[vba]Sub MakePivotCriteria1(DestSheetName As String, PlacementCell As String, SourceTableName As String, _
Optional RowFields, Optional ColFields, Optional DataFields, Optional PageFields, Optional ArithOps)

Dim n As Long
Dim pt As PivotTable
Dim vRows As Variant
Dim vCols As Variant
Dim vData As Variant
Dim vPages As Variant
'------------------------------
Set pt = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SourceTableName).CreatePivotTable( _
TableDestination:=Worksheets(DestSheetName).Range(PlacementCell), TableName:=SourceTableName)
'------------------------------------------------------------------------------------------------------------------
With pt
.ManualUpdate = True
'----------------------
If Not IsMissing(RowFields) Then
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
End If
'--------------------------
If Not IsMissing(ColFields) Then
'--------------------------
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
End If
'--------------------------
If Not IsMissing(DataFields) Then
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
.Function = ArithOps(n)
.NumberFormat = "0"
End With
Next n
End If
'--------------------------
If Not IsMissing(PageFields) Then
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
End If
'-------------------------------------
.TableStyle2 = "PivotStyleDark5"
.ManualUpdate = False
End With
End Sub

[/vba]

Called like this:
[vba]
Call MakePivotCriteria1(PiSheet, "G1", "OPEN_CALLS", "SSP NAME", , Array("SR No.", "Task Desc"), "Bus Stream", Array(xlsum, xlsum))[/vba]
for flexibility I changed the last argument to be the actual function you want used.

Works Perfect!! Thanks a Ton

I could reduce add-in file size by 2mb, thanks again

Raj