Loop through Auto Filter Criteria and Copy Formula Results Into New Sheet

**hamidxa** · 05-19-2014, 05:43 PM

I have a worksheet that contains 3 columns, A, B, C, that I need to run through auto-filter and copy the results from a cell, F2, into another sheet each time the filter criteria changes.

As an example, here is how I would envision this working for Col C:
1. Starting on the 1st Sheet (named "FW15"), I auto-filter Col C on criteria/value 1
2. I copy the resulting value from Cell F2 of sheet FW15 and paste it into the first empty cell of Col C in Sheet 2 (named "CopiedResults")
3. I return to my first sheet, FW15, turn off the enabled filter for criteria/value (1), and turn on the next autofilter Criteria/Value (of 2)
4. Repeat Step 2
.
.
Keep looping through Autofilter criteria in Col C, and copy each resulting value contained in Cell F2 over to the second sheet.

Likewise, I would need to run through the auto-filter criteria in Col A and Col B, and copy their resulting values (from cell F2) into Sheet2 Col A and Col B.

Really stuck on how to loop and copy the resulting values, and would greatly appreciate some help on this.

**hamidxa** · 05-21-2014, 09:41 AM

Bumped and post edited for clarity.
As always, would greatly appreciate any assistance offered.

Thanks in advance!

**jaslake** · 05-21-2014, 12:20 PM

Hi hamidxa

This is my perception of what you're looking for

Please Login or Register  to view this content.

**hamidxa** · 05-21-2014, 01:26 PM

Jaslake,

Thank you for taking a stab at this.
The results that were copied did not however come out as I had hoped.

Would there be a way to generate something like this, for example, if I filter against all of the criteria in Col B, to copy the corresponding cell F2 values for each B criteria:
Real examples from worksheet:

If I filter on B = 11, F2 = 0.59163
If I filter on B = 12, F2 = 0.93764
If I filter on B = 13, F2 = 1.09873

So on and so forth.

I would love to be able to see a second sheet (CopiedResults) that has results like the above example for Cols A, B, and C.

So basically the results sheet would look like this image (these are the actual values I obtain when filtering on each of the columns):
Capture.JPG

**jaslake** · 05-21-2014, 01:57 PM

Hi hamidxa

I see...the results I was getting made no sense to me.

Let me take another stab at it...get back to you...

Do you want the Results Sheet to look like the Image you posted?

**jaslake** · 05-21-2014, 02:27 PM

Hi hamidxa

See if this works better for you...

Please Login or Register  to view this content.

**hamidxa** · 05-21-2014, 03:30 PM

jaslake,

Woah, that's brilliant!

I hate to almost ask this, but what if I wanted to enable the filter in Column I to always read "Compliant" as its criteria through each iteration of these loops.

**jaslake** · 05-21-2014, 04:26 PM

Hi hamidxa

How about showing me samples of your anticipated results...several for A, B and C.

**hamidxa** · 05-21-2014, 04:51 PM

Originally Posted by jaslake

Hi hamidxa

How about showing me samples of your anticipated results...several for A, B and C.

Here you go.
Capture2.JPG

Thanks in advance, again.

**jaslake** · 05-21-2014, 05:17 PM

Hi hamidxa

Replace the Code with this

Please Login or Register  to view this content.

**hamidxa** · 05-21-2014, 05:27 PM

jaslake,

Truly grateful for all of your brilliance and consideration.
This is simply awesome.
Can't explain all of it, but i'll walk through the code and try to sort things out on my own at some point, but I definitely can see what you did there with the added criteria request.

Again, a huge thank you!

**jaslake** · 05-21-2014, 05:34 PM

You're welcome...glad I could help. Thanks for the Rep.

If this resolves your issue please mark your Thread as SOLVED.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click SaveYou're welcome...glad I could help.

Loop through Auto Filter Criteria and Copy Formula Results Into New Sheet

LinkBack

Thread Tools

Rate This Thread

Display

Loop through Auto Filter Criteria and Copy Formula Results Into New Sheet

Re: Loop through Auto Filter Criteria and Copy Formula Results Into New Sheet

Re: Loop through Auto Filter Criteria and Copy Formula Results Into New Sheet

Re: Loop through Auto Filter Criteria and Copy Formula Results Into New Sheet

Re: Loop through Auto Filter Criteria and Copy Formula Results Into New Sheet

Re: Loop through Auto Filter Criteria and Copy Formula Results Into New Sheet

Re: Loop through Auto Filter Criteria and Copy Formula Results Into New Sheet

Re: Loop through Auto Filter Criteria and Copy Formula Results Into New Sheet

Re: Loop through Auto Filter Criteria and Copy Formula Results Into New Sheet

Re: Loop through Auto Filter Criteria and Copy Formula Results Into New Sheet

Re: Loop through Auto Filter Criteria and Copy Formula Results Into New Sheet

Re: Loop through Auto Filter Criteria and Copy Formula Results Into New Sheet

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Run a loop to slected data then filter on another sheet and copy to another sheet

[SOLVED] Filter a sheet and copy results to another sheet

Filter a sheet and copy results to another sheet

Auto filter data and copy results to another worksheet?

How do I filter results to copy to another sheet and allowing modifications?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions