VBA Code to Create Pivot Tables



Function fnCreatePivot(strFilter As String, strColumns As String, strRows As String, strValues As String, strSheetName As String)
    
    'declare variable
    Dim ptCache As PivotCache
    Dim pTable As Pivottable
    Dim wksPvt As Worksheet
    
    'fnCreatePivot = True
    'Add new sheet to create pivot table
    Set wksPvt = Worksheets.Add
    
    'Rename
    wksPvt.Name = strSheetName
    
    'Create Pivot cache
    Set ptCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=wksData.Range("A1").CurrentRegion)
     
    'Create blank pivot table
    Set pTable = ptCache.CreatePivotTable(TableDestination:=wksPvt.Range("A2"), TableName:="PivotTable1")
    
    'Filter
    If strFilter <> "" Then
'    On Error GoTo Err_Next
        With pTable.PivotFields(strFilter)
            .Orientation = xlPageField
            .Position = 1
        End With
'        On Error Resume Next
    End If
    
    'columns
    If strColumns <> "" Then
        With pTable.PivotFields(strColumns)
            .Orientation = xlColumnField
            .Position = 1
        End With
    End If
    
    'Rows
    If strRows <> "" Then
        With pTable.PivotFields(strRows)
            .Orientation = xlRowField
            .Position = 1
        End With
    End If
    
    
    'Values
    If strValues <> "" Then
         With pTable.PivotFields(strValues)
            .Orientation = xlDataField
            .Function = xlCount
        End With
    End If
    
    'Release memeory
    Set ptCache = Nothing
'     Exit Function
'
'Err_Next:
'     fnCreatePivot = False
End Function