Create Multiple Pivot tables with single Pivot cache
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
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