Phelony
10-12-2011, 02:38 AM
Hi Guys
I've got the following code which completely removes fields from a pivot table:
Sub ResetPivot()
'
' Resets the pivot table and removes all data to allow the next search this works only for the columns
'in the pivot table
'
Dim pvta As PivotTable
Dim pvfi As PivotField
On Error Resume Next
Set pvta = Sheets("Debtor Pivot Analysis").PivotTables("PivotTable6")
pvt.ManualUpdate = True
For Each pvfi In pvt.PivotFields
pvf.Orientation = xlHidden
Next pvfi
pvt.ManualUpdate = False
End Sub
However, if there is only one data field (in this case: "Sum of Outstanding debt (Excl VAT)" is the data being shown in the pivot) the above code is not removing it along with the rest of the data.
For the life of me, I can't figure out how to remove the actual data. The control fields are removed easily enough but the data itself remains.
Does anyone know of a way to completely wipe a pivot table clear of fields without deleting the table though?
This is to allow a form to control a pivot table to limit the data users can extract whilst using the same pivot cache to control background tables which drive a chart dashboard.
Any suggestions would be welcomed.
Many thanks
Phel
I've got the following code which completely removes fields from a pivot table:
Sub ResetPivot()
'
' Resets the pivot table and removes all data to allow the next search this works only for the columns
'in the pivot table
'
Dim pvta As PivotTable
Dim pvfi As PivotField
On Error Resume Next
Set pvta = Sheets("Debtor Pivot Analysis").PivotTables("PivotTable6")
pvt.ManualUpdate = True
For Each pvfi In pvt.PivotFields
pvf.Orientation = xlHidden
Next pvfi
pvt.ManualUpdate = False
End Sub
However, if there is only one data field (in this case: "Sum of Outstanding debt (Excl VAT)" is the data being shown in the pivot) the above code is not removing it along with the rest of the data.
For the life of me, I can't figure out how to remove the actual data. The control fields are removed easily enough but the data itself remains.
Does anyone know of a way to completely wipe a pivot table clear of fields without deleting the table though?
This is to allow a form to control a pivot table to limit the data users can extract whilst using the same pivot cache to control background tables which drive a chart dashboard.
Any suggestions would be welcomed.
Many thanks
Phel