Consulting

Results 1 to 12 of 12

Thread: Pivot Table Creation with Optional Arguments

  1. #1

    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

    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

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
        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

  3. #3
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,720
    Location
    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]
    Be as you wish to seem

  4. #4
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,720
    Location
    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]
    Be as you wish to seem

  5. #5

    Pivot Creation by Optional Arguments

    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.

    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

  6. #6
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,720
    Location
    Yes - do give it a try and let us know if you have any problems.
    Be as you wish to seem

  7. #7
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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


         .Orientation = xlDataField 
            .Position = 1
    
            If ArithOps = True Then 
                .Function = xlCount 
                .Function = xlSum 
            End If
    



    Paul

  8. #8
    Quote Originally Posted by Aflatoon View Post
    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?

    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

  9. #9
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,720
    Location
    Quote Originally Posted by rajkumar View Post
    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.
    Be as you wish to seem

  10. #10
    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

  11. #11
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,720
    Location
    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.
    Be as you wish to seem

  12. #12

    Pivot Table Creation by Optional Arguments

    Works Perfect!! Thanks a Ton

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

    Raj

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •