Code the saves filter info to reapply after running other code

Hey Wedge120,

This is a tough question. There is .Refresh in VBA to reapply a filter and sort on a pivot table. There are Advanced Filter commands that can filter and sort data. There are Global variables that can "remember" values between running other subs. But I'm not sure you can tell VBA to do that thing that was done a while ago.

I have seen code to UNDO what a macro has done, but it had to remember all kinds of stuff. So much so it wasn't worth the effort. See http://www.j-walk.com/ss/excel/tips/tip23.htm

How about giving us an example of exactly what you are thinking and maybe it would help.

One of the smart guru's will probably have a quick answer that I'm not aware of.

In this workbook, I choose which parts I need (rows), and I place the number needed in column "R". After I enter the number needed in this column, I run a "sheetstats" macro that looks at the parts I have chosen and provides totals and other info about the parts chosen.

Before placing these quantities in column "R", I use the autofilter to gradually pick and choose parts based on the various critieria. In order to run my "sheetstats" macro, however, the filters are cleared as this macro releases them in order to make calculations.

I was wondering if there were a way to run the "sheetstats", but also see the filters that were used to make my choices.

I guess and alternative way to achieve this would be to write the "sheetstats" macro in a way that doesn't require the release of the filters, but I really don't know how.

Hi,

There is no "sheetstats" macro included in your sample file.

I'm starting to understand your problem. What if you had a helper column where you put an X in a row of the parts you have chosen. Then you could filter on this column being an x or not. Do your calculations and the x would still be there. You could create a macro to clear all the x's in that column to start over.

See the attached where I have code to double click a column to put or erase an x in it.

We need to see the sheetstats macro to make a determination on it.

Hi Wedge120,

You have about 500 lines of code that I'm falling asleep reading. Can you explain in words what you are trying to do? How do you pick the rows? What is the number in Column R for? Aren't you simply doing an advanced filter to another section of the worksheet and totals from it?

Have you looked at advanced filters instead of Auto Filter to do this?
http://www.contextures.com/xladvfilter01.html
http://www.excelfunctions.net/ExcelAdvancedFilter.html