VBA Macro to Hide Pivot Fields

[Matt1701]

Hello everyone. I'm new to the forum, and need a little help. I know just enough about VBAs and Macros to dabble. I have a pivot table that I'm trying to run numerous reports on via macros linked to buttons. So far, all of my reports work fine. However, I have to manually clear the pivot table each time in order to run the next macro. The data that the spreadsheet pivots from is about 100 columns and about 15,000 rows.

What I would like to do is create a macro that resets the pivot table to a blank stage so that I can then run the next macro. The best way I've been able to figure out how to do that would be to hide each individual field, code below:

ActiveSheet.PivotTables("PivotTable1").PivotFields("1-Item #").Orientation = _
xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("2-Date").Orientation = _
xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields("3-ACCT#").Orientation = _
xlHidden

This works, for the first 47 fields, then for some reason gives me an error on the 48th. Since I've got over 100 fields, this won't work. Is there a way that I can simply have a code that hides all pivot fields so that the pivot table resets back to blank stage? If not, is there a way to fix the fact that I'm getting hung up on the 48th field? FYI, I'm running Excel 2003. Thank you all for your help.

 

[dk]

Hi

There may be an easier way but this works for me. I tested it by putting 50 pivot fields into a pivot table and it ran without problems. Let me know if it works for you.

Sub ResetPivotTable()
Dim pvt As PivotTable
Dim pvf As PivotField



Set pvt = Sheets("Sheet1").PivotTables(1)

pvt.ManualUpdate = True

For Each pvf In pvt.PivotFields

    pvf.Orientation = xlHidden
    
Next pvf

pvt.ManualUpdate = False


End Sub

[Edit] This code will leave your data items i.e. the $ values are whatever in the pivot table. I would think that this shouldn't matter although let me know it it does[/Edit]

HTH
DK

 

[Matt1701]

Dk, that worked like a charm. Thanks so much. But yes, it does leave the data items. Is there a way to clear those out too, so that you get back to a completely blank pivot table? Thanks so much for your help.

 

[Matt1701]

Dk, nevermind. I was able to figure out how to take out the data fields myself, by adding a couple of lines to your code and changing pivotfields to datafields. Thanks you so much for your help!

 

[dk]

No worries, glad you got it sorted.