PDA

View Full Version : VBA: adjusting pivot table per macro



JackDaniels
06-01-2016, 04:38 AM
Hello Excel friends,

I have a pivot table and the following code:

Sub pivot_tables()




Dim location As String
Dim year As Integer



location = Worksheets("event detection").Range("B1")


ActiveSheet.PivotTables("PivotTable1").PivotFields("Florida 2017").Orientation = xlHidden

ActiveSheet.PivotTables("PivotTable1").PivotFields("Florida 2016").Orientation = xlHidden

ActiveSheet.PivotTables("PivotTable1").PivotFields("Florida 2015").Orientation = xlHidden

ActiveSheet.PivotTables("PivotTable1").PivotFields("Florida 2014").Orientation = xlHidden

ActiveSheet.PivotTables("PivotTable1").PivotFields("Florida 2013").Orientation = xlHidden

ActiveSheet.PivotTables("PivotTable1").PivotFields("Florida 2012").Orientation = xlHidden


ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1"). _
PivotFields(location & " 2012"), "number of people in 2012", xlSum

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1"). _
PivotFields(location & " 2013"), "number of people in 2013", xlSum

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1"). _
PivotFields(location & " 2014"), "number of people in 2014", xlSum

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1"). _
PivotFields(location & " 2015"), "number of people in 2015", xlSum

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1"). _
PivotFields(location & " 2016"), "number of people in 2016", xlSum


ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh


ActiveSheet.PivotTables("PivotTable1").PivotFields("country").AutoSort _
xlDescending, "number of people in 2015"




End Sub

Only these 6 fields existed and I deselected them in the fields list during the macro recording.
My goal is to deselect all fields in the pivot table at first, regardless of the amount and names, so I came up with the following code which is a perfect solution:

Dim PT As PivotTable, PTField As PivotField



Set PT = Sheets("data - 1").PivotTables("PivotTable1")


With PT
.ManualUpdate = True
For Each PTField In .DataFields
PTField.Orientation = xlHidden
Next PTField
.ManualUpdate = False
End With

My other goal is to select all fields in the field list which correspond to the location & " " & year syntax. The thing is though that the location information is derived from the range B1 in the sheet event detection and the year information is derived from a range of variable length. The location part is done but how can I make the macro select variable amounts of fields based on the information in the respected ranges?


Big thanks in advance!






Jack