Solved: Remove Data from Pivot Table

[Phelony]

Hi Guys

I've got the following code which completely removes fields from a pivot table:
[VBA]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[/VBA]


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

[Aflatoon]

I believe this will work (I also amended the mistakes in the variable names):
[vba]

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 = ActiveSheet.PivotTables(1)
pvta.ManualUpdate = True
For Each pvfi In pvta.DataFields
pvfi.Orientation = xlHidden
Next pvfi

For Each pvfi In pvta.PivotFields
pvfi.Orientation = xlHidden

Next pvfi
pvta.ManualUpdate = False

End Sub
[/vba]

[Phelony]

Thank you for that, works exactly as originally intended.

So naming the pivot table itself was an error?

[Aflatoon]

No - apologies, I should have reset the sheet/pivot table name to the ones you were using. Your code sometimes used pvt and sometimes pvta, and similarly pvfi and pvf, so I am surprised it did anything at all. On that subject, if you used Option Explicit at the top of your code modules, you would have noticed the mistakes very quickly.

[Phelony]

Dang, thought I'd caught all of those! Not using Option Explicit is a bad habit of mine!

Thanks again for your help.

[Aflatoon]

Glad to help. I would recommend that you set your VBEditor to automatically add Option Explicit to all modules. Tools - Options - Editor tab, 'Require variable declaration'