Create Multiple Pivot tables with single Pivot cache

  • This program enabls the user to create multiple pivot tables using single pivot catche
  • Primary Object is to create multiple pivot tables using Userform
  • User can select the data through Userform for Row field, column field, Data Field, page field baed on requirement
  •  

    Click on below mentioned image to watch video:

    Download The Workbook

     

    Code In Module:

    Public PC As PivotCache

    Sub CratePivotCatche()

    Dim Bsh As Worksheet
    Set Bsh = ActiveWorkbook.Sheets("Buttons")

    Dim SrcRng As Range
    Set SrcRng = Bsh.Range("A1").CurrentRegion


    Set PC = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcRng)
    ActiveWorkbook.ShowPivotTableFieldList = True


    PC.RefreshOnFileOpen = True

    Dim LastCol As Integer
    LastCol = Bsh.Range("A1").End(xlToRight).Column


    For C = 1 To LastCol
    UserForm1.ComboBox1.AddItem Bsh.Cells(1, C).Value
    UserForm1.ComboBox2.AddItem Bsh.Cells(1, C).Value
    UserForm1.ComboBox3.AddItem Bsh.Cells(1, C).Value
    UserForm1.ComboBox4.AddItem Bsh.Cells(1, C).Value
    Next

    '===============Add Functions==============
    UserForm1.ListBox1.Clear
    UserForm1.ListBox1.AddItem "xlSum"
    UserForm1.ListBox1.AddItem "xlCount"
    UserForm1.ListBox1.AddItem "xlAverage"
    UserForm1.ListBox1.AddItem "xlMax"
    UserForm1.ListBox1.AddItem "xlMin"
    UserForm1.ListBox1.AddItem "xlCountNumbers"

    UserForm1.Show
    End Sub

     

     

    Code In Module:

    Private Sub CommandButton1_Click()

    Dim RowField As String
    RowField = Me.ComboBox1.Text

    Dim ColField As String
    ColField = Me.ComboBox2.Text

    Dim DataField As String
    DataField = Me.ComboBox3.Text

    Dim FunctionName As String
    FunctionName = Me.ListBox1.Value

    Dim PageField As String
    PageField = Me.ComboBox4.Text


    Set Nsh = ActiveWorkbook.Sheets.Add(after:=Sheets(Sheets.Count))

    Dim Pv As PivotTable
    Set Pv = PC.CreatePivotTable(TableDestination:=Nsh.Range("A1"), TableName:="Sales")


    If FunctionName = "xlCount" Then
    FunctionNumber = -4112
    ElseIf FunctionName = "xlSum" Then
    FunctionNumber = -4157
    ElseIf FunctionName = "xlAverage" Then
    FunctionNumber = -4106
    ElseIf FunctionName = "xlMax" Then
    FunctionNumber = -4136
    ElseIf FunctionName = "xlMin" Then
    FunctionNumber = -4139
    ElseIf FunctionName = "xlCountNumbers" Then
    FunctionNumber = -4113

    End If

    With Pv
    .PivotFields(ColField).Orientation = xlColumnField
    .PivotFields(RowField).Orientation = xlRowField
    .PivotFields(PageField).Orientation = xlPageField

    With Pv.PivotFields(DataField)
    .Orientation = xlDataField
    .Function = xlSum
    .Function = FunctionNumber

    End With
    .TableStyle2 = "PivotStyleLight17"

    End With
    Nsh.Name = Me.TextBox1.Value

    End Sub

    Private Sub CommandButton2_Click()
    Unload Me
    End Sub