VBA to filter multiple items

Can you post a sample workbook that have this data would be easier to manipulate
gowflow

reading your post again I feel there is something not clear.

You say first I have a sheet1 that has data in Col N ... like data below
then you say if in Col A data is CA02 I want to copy all occurences of CA02 the entirerows to Sheet10


Col N and Col A makes the thing that is not clear.


Here is my understanding based on the sample you provided you want the final outcome to be

Sheet10
CA02      CAEU
CA02      CALS
CA02      CAOT
CA02      CAUS
CA02      CAGB
CA02      CORP
CA02      CAKL
CA02      CALN
CA02      CANT
CA02      CAEX
CA02      CALQ
and for sure the entire row if there is more data in the columns to also show

Sheet11
CA03      CAEU
CA03      CALS
CA03      CAOT
CA03      CAUS
CA03      CAGB
CA03      CORP
CA03      CAKL
CA03      CALN
CA03      CANT
CA03      CAEX
CA03      CALQ

Sheet12
CA08      COKL
CA08      COMT
CA08      COPL


Is this correct ???
gowflow

I have attched a file with data in column 'N' in Sheet1 with Sheets 10 11 and 12 blank except for their header row. There is also a hidden tab called static with the details CA03      CAEU
etc.

So for sheet 1 row 1 where 'N' is CAEU this would look at the table in 'Static' and identify that it is CA02 so the entire row in Sheet1
xxx      www      qqq      ggg      uuu      ddd      24/112013      aaa      lll      19/11/2013      tttt      ccccc      hhh          CAEU      zzzzz      yyyy      0-3

would go on Sheet 10 in row 2 [See Sheet Example]

It may be that my table needs to be turned around so that it becomes

    CAEU      CA02
    CALS      CA02
    CAOT      CA02
    CAUS      CA02
    CAGB      CA02
    CORP      CA02
    CAKL      CA02
    CALN      CA02
    CANT      CA02
    CAEX      CA02
    CALQ      CA02
    CAEU      CA03
    CALS      CA03
    CAOT      CA03
    CAUS      CA03
    CAGB      CA03
    CORP      CA03
    CAKL      CA03
    CALN      CA03
    CANT      CA03
    CAEX      CA03
    CALQ      CA03
    COKL      CA08
    COMT      CA08
    COPL      CA08

and that would be fine.

thanks in advance
Filter.xlsx

Noted but still not clear.
CAEU exist for CA02 and CA03 why should it go on sheet10 and not sheet11 ?
gowflow

my mistake it should only be CA02

I have had a look at the file and clearly missed the fact that each group do in fact have the same codes so for each group add A in front of those in CA02 B in CA03 and C in front of those in CA08

Sorry for the confusion.

Please post an other workbook modified like you want with clear instructions based on clear codes so I can give you my comments.
gowflow

I have uploaded a new spreadsheet.

Sheet 'example' shows how all items in Row 'N' that correspond to the table in static go together and would be copied to another sheet. i.e.sheet10 The best way I can describe this is that if I were to do a Vlookup of column N against the table in the tab 'static' they would look like this

ACAEU      CA02
ACAEU      CA02
BCAUS      CA03
BCAUS      CA03
BCAUS      CA03
BCAUS      CA03
ACAEU      CA02
ACAKL      CA02
BCALS      CA03
ACAKL      CA02
CCOMT      CA08
CCOMT      CA08
ACAKL      CA02
 ACALQ      CA02
 ACALQ      CA02
 ACALQ      CA02
 ACALQ      CA02

so all items rows that correspond to CA02 would be copied to Sheet10 all rows that correspond to CA03 would be copied to Tab 11 etc.

I hope this helps

Thanks
Filter-1-.xlsx

ok fine have several questions:

1) Do we need to keep data in sheet1 or we can cut paste into the new sheets ?
2) IF sheet10, 11, 12 ,,,, does not exist then we create the sheet
3) If the sheet10, 11, 12 ... do exist shall we add to that sheet at the bottom of the existing data the new data we found
or
we delete what we have in those sheet and paste the data fresh ?

Like do you intend to just distribute sheet1 in several sheets and that's it or it is a continuous workbook that will keep receiving data in sheet1 and the macro will always distribute in several sheets at the end of existing data ??

I ask these question coz usually I provide full final working solution not just some code that leave you with the headache of having to adapt to your final need just for the sake of getting points !!!!

Reason why I ask for clarity ! :)
gowflow

Hi gowflow

1) Do we need to keep data in sheet1 or we can cut paste into the new sheets ?

We need to keep the data in sheet 1

2) IF sheet10, 11, 12 ,,,, does not exist then we create the sheet

These will always exist

3) If the sheet10, 11, 12 ... do exist shall we add to that sheet at the bottom of the existing data the new data we found

we delete what we have in those sheets and paste the data fresh with the header row

4) Like do you intend to just distribute sheet1 in several sheets and that's it or it is a continuous workbook that will keep receiving data in sheet1 and the macro will always distribute in several sheets at the end of existing data

Each day the workbook will get new data on these sheets. I have other sheets in the workbook that I add new data to which I already do. It is distributed as a complete workbook

thanks for your assistance

ok noted will do
gowflow

One more
Suppose one day you only get data for CA03 will this data go to sheet10 or sheet11 ?

Case it will go to sheet11 (which is not in accordance with the logic you already advised that the first item encountered will go sheet10 etc ...) then would you have any objection that we add in the hidden sheet Static a column C where we put the final destination it will lokk like this:

ACAEU      CA02      Sheet10
ACALS      CA02      Sheet10
 ACAOT      CA02      Sheet10
 ACAUS      CA02      Sheet10
ACAGB      CA02      Sheet10
ACORP      CA02      Sheet10
ACAKL      CA02      Sheet10
ACALN      CA02      Sheet10
ACANT      CA02      Sheet10
ACAEX      CA02      Sheet10
 ACALQ      CA02      Sheet10
BCAEU      CA03      Sheet11
BCALS      CA03      Sheet11
BCAOT      CA03      Sheet11
BCAUS      CA03      Sheet11
BCAGB      CA03      Sheet11
BCORP      CA03      Sheet11
BCAKL      CA03      Sheet11
BCALN      CA03      Sheet11
BCANT      CA03      Sheet11
BCAEX      CA03      Sheet11
BCALQ      CA03      Sheet11
CCOKL      CA08      Sheet12
CCOMT      CA08      Sheet12
CCOPL      CA08      Sheet12


Pls advise
gowflow

It will always go to the same sheet. Adding in the sheets as in your example is a very good idea

Thanks

ok great. This way you could simply update endlessly this static sheet with what ever new sheet reference you want with whatever new code that there is and the macro will work accordingly !!! No code modification necessary.

I am just puzzled at the fact that you don't want (after distribution is performed) to delete the data that is in Sheet1. The only logical answer for this would be that after running this macro you would be running other macros that would update other sheets based on sheet1 data reason why you would still need this data to be there. Is this statement correct ?

One last thing, is there a possibility somewhere to have the Code CA02, CA03 ... exist in the data in sheet1 in a certain column when you get this data downloaded or it is not a possibility ?

gowflow

I am just puzzled at the fact that you don't want (after distribution is performed) to delete the data that is in Sheet1. The only logical answer for this would be that after running this macro you would be running other macros that would update other sheets based on sheet1 data reason why you would still need this data to be there. Is this statement correct ?

Correct

One last thing, is there a possibility somewhere to have the Code CA02, CA03 ... exist in the data in sheet1 in a certain column when you get this data downloaded or it is not a possibility ?

No not possible

ok great. This way you could simply update endlessly this static sheet with what ever new sheet reference you want with whatever new code that there is and the macro will work accordingly !!! No code modification necessary.

this sounds brilliant. Thanks

perfecto !
let me go to work now !!! :)
gowflow

ok here it is:

1) This version will create the sheet if they don't exist

2) Have in Static a reference to the sheets name you need to have in Col C that are user set

3) If a code in Col N is not found in sheet Static then it will be reported in a sheet called SheetMissing this would indicate that you will need to go back and add or modify data in Sheet Static to reflect the correct data. When this happens you will simply after updating sheet Static re-run the macro and it will update the data correctly. You may try this situation by simply changing the code in Sheet1 for any row so it does not find it in sheet static.

4) The Sheet1 at some point is being Sorted by sheet like the initial sequence of data in Sheet1 is not the same that will be copied onto the final sheets and also is not the same as originally posted in sheet1 if this is a problem let me know then I will need to revise the logic.

Please try this version extensively and revert with your comments.
gowflow

did you intend to include a file?

Thanks gowflow. I will, as you say, check it extensively and revert with my comments.

In the meantime have a great weekend

Rgds

u too.
gowflow

brilliant it works perfectly,

Excellent. Please feel free to let me know if you would need any more help by posting it in here. I will keep this question monitored.
gowflow

gowflow,

apologies for pestering you on this one again but I have just discovered [a bug maybe] something that I was not aware of.

Where it captures the data from the Static tab and puts it on the relevant tab, it's brilliant. Where it creates a TAB 'sheetmissing' it falls over. Say for instance there are 15 items to put onto the sheetmissing tab, for some reason it is trying to go beyond the last row [1048576] and falling over.

"Loop Until ColZZ <> WS.Cells(J, "ZZ")" Application defined or object defined error



Can you assist

P.S. It then comes up with "Excel cannot  complete this task with available resources. Choose less data" ?????

let me look at it will revert v shortly. Sorry for the inconvenience.
gowflow

Could  you post the workbook it would help clear it quickly ??
gowflow

Thanks gowflow
testfile.xlsm

ok sorry for that try this one and let me know.

to be clear on what I changed you will need to replace these 2 lines

For I = 2 To MaxRow
    
    If ColZZ <> WS.Cells(I, "ZZ") Then

Select allOpen in new window

By these 3 lines

For I = 2 To MaxRow
    If WS.Cells(I, "ZZ") = "" Then Exit For
    
    If ColZZ <> WS.Cells(I, "ZZ") Then

Select allOpen in new window

gowflow
testfile.xlsm

thanks gowflow that works fine now.

Regards

Your welcome sorry for the inconvenience.
gowflow