Dynamic Pivot Table Based on Drop-Down List Items

[marduino]

Hello,

I am trying to setup a VBA script to generate a pivot table based on user selections from drop-down (and dependent-drop down) menus of items taken from "Table1". I've done a bit of looking around online and I can't seem to find anyone else having tried to accomplish this, or if this is even possible, so I am turning to the experts here for help!

I've attached an image of the cell layout for the drop-down menus, and an example of one of the pivot table results I am trying to automate. The issue that I am having is that I am not sure how to go about dynamically referencing the the drop-down inputs in the script for the "PivotFields(...)" items. Below is a template for the code, the B2, B3, B6, etc...values are serving as a placeholder for where the input values should go. In theory I think that the PivotField items should reference a column number in the source data (which i can use a MATCH formula to output), but I am still not sure how to properly code that or reference filters that are derived from dependent drop-drop down items.

VBA:

Sub PivotTable()
PivotCaches.Clear
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Table1", Version:=xlPivotTableVersion14).CreatePivotTable TableDestination _
:="'WIN%'!R1C5", TableName:="PivotTable1", DefaultVersion:= _
xlPivotTableVersion14
Sheets("WIN%").Select
Cells(1, 5).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields(B2)
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields(B6)
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields(B8)
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields(B2)
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields(B2).ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields(B2).CurrentPage = _
B3
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("WIN"), "Sum of WIN", xlSum
ActiveSheet.PivotTables("PivotTable1").CalculatedFields.Add "# of RACES", _
"=WIN+LOSS", True
ActiveSheet.PivotTables("PivotTable1").PivotFields("# of RACES").Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable1").CalculatedFields.Add "WIN%", _
"=WIN/(WIN+LOSS)", True
ActiveSheet.PivotTables("PivotTable1").PivotFields("WIN%").Orientation = _
xlDataField
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Sum of WIN%"), "Sum of WIN%", xlSum
End Sub


As always, thank you for your time and efforts!

 

[DanteAmor]

See if the following helps you.
First, if the pivot table doesn't exist, you can create it with the following macro:

Sub CreatePivotTable()
  ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
    SourceData:="Table1").CreatePivotTable _
    TableDestination:="'WIN%'!R3C5", TableName:="PivotTable1"
End Sub

If the pivot table already exists, then the data in the cells should look like this:

varios 15may2022.xlsm
	A	B
1	PAGE
2	INPUT 1	JOCKEY
3	FILTER 1	Luis
4
5	ROW
6	INPUT 1	SURFACE
7	FILTER 1
8	INPUT 2	DISTANCE (F)
9	FILTER 2
10
11	VALUES
12	INPUT1	WIN
13	INPUT2	# of RACES
14	INPUT3	WIN%
WIN%

Try the following macro to populate the pivot table.

Sub PivotTable()
  Dim sh1 As Worksheet
  Dim tbl As PivotTable
  Dim input1 As String, input2 As String, input3 As String
  
  Set sh1 = Sheets("WIN%")
  Set tbl = sh1.PivotTables("PivotTable1")
  tbl.ClearTable
  tbl.PivotCache.Refresh
  
  'Page
  With tbl.PivotFields(Range("B2").Value)
    .Orientation = xlPageField
    .Position = 1
    .ClearAllFilters
    .CurrentPage = sh1.Range("B3").Value
  End With
  
  'Rows
  If Range("B6").Value <> "" Then
    With tbl.PivotFields(Range("B6").Value)
      .Orientation = xlRowField
      .Position = 1
    End With
  End If
  If Range("B8").Value <> "" Then
    With tbl.PivotFields(Range("B8").Value)
      .Orientation = xlRowField
      .Position = 2
    End With
  End If
  
  'Values
  input1 = sh1.Range("B12").Value
  If input1 <> "" Then
    tbl.AddDataField tbl.PivotFields(input1), "Sum of " & input1, xlSum
  End If
  input2 = sh1.Range("B13").Value
  If input2 <> "" Then
    tbl.CalculatedFields.Add "# of RACES", "=WIN+LOSS", True
    tbl.AddDataField tbl.PivotFields(input2), "Sum of " & input2, xlSum
  End If
  input3 = sh1.Range("B14").Value
  If input3 <> "" Then
    tbl.CalculatedFields.Add "WIN%", "=WIN/(WIN+LOSS)", True
    tbl.AddDataField tbl.PivotFields(input3), "Sum of " & input3, xlSum
  End If
End Sub

Results:

Note: I did not integrate the filters for the rows, but check if you can integrate them.