Auto filter delete after copied

Hi,

My code below filters data in one tab then takes the top 10 values and copy and paste them into a three new tabs. The code works but how do I delete the rows from the original tab after they have been copied to the new tabs?

Please Login or Register  to view this content.

h

For example, filter for the first one (Array (10, "New Dead")). Paste the top values into the new tab. Then delete the top 10 values data from the tab it was filtered in. Then move on to the next one (Array(12, "New Excess"))

Untested

Please Login or Register  to view this content.

I keep getting an "Application defined or object defined" error

Then need to see you workbook with before/after.

I attached an example workbook.

The full tab has all the data then the data gets filtered onto the "New" tabs. In the tab "New Excess" and "New slow" you can see I highlighted a couple rows in red.

The "New Excess" tab should have the top ten values for the excess qty and "New slow" should have the top 10 for the slow qty.

But right now some rows are showing up on both tabs because they are in the top 10 for both, (the red ones). I don't want this to happen I want it to only show up once.

So I am trying to have the row be deleted after it is filtered so it will only show up in one of the tabs.

Why do all new sheets have 2nd row blank?

Originally Posted by jindon

Why do all new sheets have 2nd row blank?

That is a bug in the code I haven’t been able to figure out yet. They shouldn’t be blank.

OK, you said some names are in the both new sheets in your workbook colored red.

What do you want to do with these?
Appear to a specific new sheet only?
If so, what is the logic?

They are Top 10 for different column, so it could happen...

Sorry I forgot to include that - I would like them to occur in the top ten dead first, then top ten excess, then top ten slow.

So if they are in both excess and slow I would like it to show in excess.

Top 10 for each name for each column that have greater than 0.
Results are slightly different from yours, so check.

Please Login or Register  to view this content.

It works like a charm!!!! Thank you!!!

I keep trying to add a filter in so it only pulls data that has column 18 blank. I had an auto filter for it in my old code but every way I try to add it in here I keep breaking it :/

The filter in my old code:

Please Login or Register  to view this content.

Also, I added more people in the below and it isn't doing it for all of them. It will only do it for the first name then lump all the other names together.

Thank you for all the help on this, I really appreciate it!

Please Login or Register  to view this content.

1) Why?

Please Login or Register  to view this content.

Col.18 = Col.R and have no heading/data at all.

2) Do you want to do the same for all the unique name in Col.D, or only the names that have worksheet?

1. It is a column in the report that they will be adding - it will be a column or dates. So yes there will also be a header there called "Date"

2. All unique names. So exactly what that code does with those two names but for more. So there will be in total 10 names.

Try

Please Login or Register  to view this content.

Wow!!! Works Like a charm with all the names!! My old code ran so much slower... so thank you!!!

The only thing I noticed is the data with a date in column 18 is still getting pulled in. Is there a way so any data that has a date in column 18 won't be included?

Just change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Then it will still pull in the rows with data in column 18. It just won't pull in column 18 but the rest of the row (1-17) will still be there.

I am looking for it to exclude any rows with data in column 18 from being filtered.

It would help to understand what you are trying to do if you upload your workbook and result that you want.

I attached a new workbook. As you can see the first tab has data in red that has a date in column 18. That data then is not filtered in to any of the three "New" tabs.

If you want to exclude the rows that have data in Col.R then add lines in bold.

Please Login or Register  to view this content.