Macro to filter data and copy to another workbook

**Roop** · 05-10-2011, 12:53 PM

Hi Guys,
I am trying to write a macro that will filter the data out of one workbook and paste it in another based on the criteria provided for in column A and C. The criteria for A is fixed say
"1" but the criteria for column C is going to be an array for 200 different items.

Also i want the macro to look through all the worksheets of a workbook as i have three worksheets with 65536 line items. and copy data to new workbook.

I have attached a dummy data sheet for reference.
So, my criteria for coulm A is "1" and array of criteria for colimn C is (" AA, BB, , DD, FF, GG, HH, JJ").

I want to macro to set Column A on "1" and then cycle through the criteria on Column C copy and pasting the data in to new work book, it will reset the filters each iteration. If there is error like it cant find any criteria in column C show message and continue to next criteria .

Any help will be highly apprecited.

Thanks.
Roop

**Roop** · 05-10-2011, 01:51 PM

I just thought may be i don't need to cycle through the criteria on Column C, it will be great if it selects all the concerned choices and then copies all the data to new woorkbook.

**realniceguy5000** · 05-10-2011, 04:26 PM

Hi,

Maybe something like this will work for you,

I placed a button on sheet(1) to run the script.
It will filter each sheet by the number that is placed in the input box.
it will save to you C: Drive by default you can change the path to soot your needs.
The file name will be either
Results 1
Results 2
Results 3
Depending on the choices made.
After the first choice it will ask you if you want to continue and loop till all choices (ect 1,2,3) are complete.

Let me know of any issues or changes required.

**Roop** · 05-10-2011, 05:45 PM

Hi Mike,
This looks too complicated for me, I just want a macro which filters column A on option "1" . then filters Column C for ("AA","BB","CC", "DD","FF","GG", "HH", "JJ"), Copy all that data to another workbook.

Now the real problem is that my data is scattered over three worksheet as i was not able to fit on one. I want macro to look through all three sheets, i dont know if i can do that. May be i have to start at sheet1 pull out what i find there, then go sheet2 pull put what i find on that andso on. My list of criteria for column C is 200 items long and they might not all be on the same sheet. I don't really know how many criterion i can give in a macro.

I really appreciate your help

Roop.

**Roop** · 05-10-2011, 10:04 PM

Hi Guys ,
So, i got this Macro that filetr data in sheet1 according to criteria given on sheet2 and copies the information to sheet3. Now my original data is apnned over Sheet1, Sheet2 and Sheet3 ( all 65536 rows), Is there a way to change this macro to go through all three sheets an copy the matching data to new workbook , because unlike the Dummy data sheet attached the macro will not find all the information matching requested criteria on one sheet.

Any help will be highly appreciated

Thanks.

Roop

Please Login or Register  to view this content.

**realniceguy5000** · 05-11-2011, 01:55 PM

Give This A Try,

This Will Filter by col A BY 1
This Will Filter by col C BY ("AA","BB","CC", "DD","FF","GG", "HH", "JJ")
This Will Add a New Workbook for each Filter In C
This Will Also Give you an option to put all the filtered workbooks back together into one with each sheet in that workbook being the filter list from C or you can just leave them in seperate workbooks by clicking no.

Let me know,