Clone AutoFilter object in Excel

May I know if it is possible to clone an AutoFilter object in Excel 2010?

My purpose is this:

I have got a number of source data tables in 1 sheet.  I'd like to manipulate the data filtering in the source tables in code, copying the filtered results to other sheets.

At the end, I'd like to restore the AutoFilters originally set in the source tables.

Thanks,
Janice
janicepAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
I can only think of two real options (there is no simple cloning facility, sadly):
1. Copy the sheet and manipulate the copy of it.
2. You would have to loop through all the tables, loop through each filter in the table, check if it's on and if so, store the operator and both criteria values. Then after your manipulation is done, you would need to reverse this process.

option 1 is obviously simpler, if a bit clunky. Would it work for you?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
janicepAuthor Commented:
I guess I will need 2.  At least I know I am not going to do something easy in an overly complex way.  Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.