Using an autofilter instead of LOOP

Hi All

I have an spead sheet with 9 sheets 1 master and 8 sub sheets. The master sheet runs for col A to AV and the it can have x amount of rows. At the moment i use the following but this takes an age.

Please Login or Register  to view this content.

The first part removes any duplicates which i need but the next part loop looks for each name in col C copies the row and then paste it in the relevent sheet.

I know this can be done using the a auto filter macro, but i can't get my head around these. Can someone please point me in the right direction.

Also is there a set code that i could use for the auto filter and just change what i'm filtering by, i use the above loop often but its time consuming and inpractical.

You should come back and let us talk some about speeding up recorded macros by editing out the "selecting"...merging multiple lines of human activity recorded into one line of VBA command.

Meanwhile, take a look a this stock macro of mine. It does what you're doing. The specifications of the macro are:

1) Create a "list" in memory of all the unique values found in column A
2) Put that list into a VBA array
3) Going through the list one unique item at a time, use an AUTOFILTER on that data
4) If a matching sheetname does not exist already, create it
5) Copy the filtered data to the matching sheet
6) Give a report of how many rows of data were copied as compared to how many rows there are TO copy, they should match!

Since the macro brings over the headers from the original macro, too, we don't fiddle with that stuff.

This should give you some ideas you can apply, or you can just tweak it to filter your sheet as is:

Please Login or Register  to view this content.

If you state in words what you'd like your macro to do (don't refer to it as is), tweaking my macro to accommodate shouldn't be too hard.

1) List unique values in column ??
2) AutoFilter that column by each value and copy to sheet with same name
3) During the copy, erase data on target sheet so copy is fresh each time

Like that?

The macro recorder is recording your human activities. It doesn't give optimized code. I've merged as many as 10 lines of recorded code into a single line of direct VBA. And of course, eventually you'll discover ways to do things in a single line of VBA that the recorder could never suggest. For instance, most people loop through a column to delete blanks by evaluating the cells one at a time, but the SpecialCells feature can delete ALL of them in one step!

I only use ACTIVATE if I want to take a shortcut in writing my commands. SELECT is eliminated 99% of the time (some printing VBA still wants things selected...very odd). As long as you qualify all the commands with parent objects, you can write and entire macro with no activating or selecting.

If you want to see what I mean about merging, try this:

1) Turn on the macro recorder
2) Click on a different cell
3) Type a word into that cell
4) Click on that cell again and set the font to a different size
5) Add a bottom edge border to the same cell
6) Turn off the recorder

Now look at the code. You only did 3 things, but you have 15-20 lines of code. Can you merge it down to only 3 things you did, thus 3 lines of code? Good exercise.

Originally Posted by E3iron

here we go.

1) unique values, (Ian Parsons, Trevor Lees, Brian Hunt, kate Stapleton,Victoria Carpenter, Alex Hernandez, Matthew Thomas, Matthew Wells.

Oops, you misunderstood. A good macro won't require you to manually list the unique values, though you CAN, a good macro gets the unique values on its own. So, I would use the ADV FILTER on column L to get a list of the names used.

This way, it's always automatic.

Here's the macro adjusted to use column L instead of A:

Please Login or Register  to view this content.