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