Copying whole range into a new sheet based on cell value

[frustrated_macro]

Hi - i have a huge document to sort through and split into 27 different tabs. basically, everything that in column A says "apple" i need all of the rows copied into the "apple" tab and so on, header row included
There will be a fixed number of columns, but this report will grow in length each week so i would need it to find apple in lines a2 to infinity and copy all the rows from columns from A to L over to each tab

 

[Joe4]

Why are then using actual physical data filters instead of the FILTER function?
The FILTER replaces the need for physical data filters!
You just build the criteria right into the FILTER function!

Just separate each criteria with a "*", as shown here: How To Use Excel FILTER Function With Multiple Criteria

 

[frustrated_macro]

See if this variation works:

Range("A2").Formula2 = "=FILTER('SOURCE DATA'!A2:M10000,ISNUMBER(SEARCH(A1,'SOURCE DATA'!A2:A10000)))"

thanks joe!

 

[frustrated_macro]

Why are then using actual physical data filters instead of the FILTER function?
The FILTER replaces the need for physical data filters!
You just build the criteria right into the FILTER function!

Just separate each criteria with a "*", as shown here: How To Use Excel FILTER Function With Multiple Criteria

oh, are you talking about the AutoFilter. ?
i need to keep the filtered results still available for use after the users update the sheets. so if i use the FILTER function, like i did at the top of the code, it would get rid of them and i still need to see them
so i just need to figure out how to get it to skip that line if it cant any

 

[Joe4]

oh, are you talking about the AutoFilter. ?
i need to keep the filtered results still available for use after the users update the sheets. so if i use the FILTER function, like i did at the top of the code, it would get rid of them and i still need to see them
so i just need to figure out how to get it to skip that line if it cant any

No! Autofilter is something very different. Autofilter is NOT a function! Excel has a new FILTER function (something brand new in the past few years) that I think is even better than Autofilter.
If you take a look at the link I provided, it explains it and shows you EXACTLY how to use the FILTER function.

If you set up the range and criteria in the FILTER function properly, it should be dynamic, meaning once you set up the formula initially, you should not have to edit it again and it will dynamically show you all the data meeting the criteria, even as your source data changes.