seeking a marco to reset AutoSort in Pivot Tables

[Randy Wellman]

I would like to add a marco to my worksheet that will automatically reset all the AutoSorts to "all" in my Pivot Table. Can anyone help? I'm familiar with Pivot Tables but new to macros.

[GeneralDisarray]

Can you attach an example worksheet? How are you looking to run the macro (button, worksheet activation event, etc...)

[Randy Wellman]

Thanks for the response. Unfortunately I can't attach an example. I'm just looking to reset all the drop down field selectors (AutoSort) in a couple of Pivot Tables to "(Show All)". I've learned how to record a macro, but in this case the key strokes can not be defined because the number of times you manually select (Show All) in the drop down depends on whether it was initially selected or not. So I'm looking for some kind of function or command to do this that does not required the logic of first looking at what is selected now. I would like to add this to my Auto_Open macro that I recorded. I don't really need a button.

[GeneralDisarray]

This worked for me. I tried it on a table with many thousands of 'pivotItems' and seems to work.

The code will refresh, and unfilter all the pivot tables on the active sheet.

Let me know what you think

Please Login or Register  to view this content.

[Randy Wellman]

Thanks for taking the time to recommend a macro, but I could not get it to work. I suspect I'm not doing a good job of explaining my need. So I've attached a different file that I can use to explain my need (I still can't post the actual file). The first worksheet of the attached workbook is a Pivot Table and the first autosort (column A) is selected on "Expenses". I've added your recommended macro, but the autosort does not reset to "(Show All)". What I'm looking for is a macro that resets all the the autosorts in this Pivot Table in row 6, columns A through D to "(Show All)". (The actual worksheet I need to work on has more columns). With this example perhaps my request will be more clear. Thanks again for your help and I look forward to your next recommendation.

[GeneralDisarray]

hmm.. not sure what to tell you. I just downloaded that file, ran the macro, and it worked like a charm. After running it, the filters were removed. I'm not sure what you mean by "autosort" - i think you mean filters.

There are a couple of possible issues. First, the macro was written to act on the "active" sheet. You may have run the macro while a different sheet was active.

But, possibly this is a excel version issue. The code works fine on 2007/2010 (and actually on the 2011 version i'm running on the mac i'm using at the moment). Pivot tables were quite different in excel 2003 (is that what you are using?) maybe the code is no good there.

Try the workbook you have, launch the code (run the macro) while you are actively viewing he Performance PIVOT sheet. Or, download the attachment which will always reference that sheet.

[Randy Wellman]

Thanks again for your help. This must indeed be a version issue. I've downloaded your edited attachment and found no success when using my version 2003 of the software. I've learned that our systems will be upgraded in August so I'll just wait until then and implement this solution on the new version. Thanks.