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
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