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]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[frustrated_macro]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks! I have excel 365!

 

[frustrated_macro]

okay, i found this code and im using it- and its working


Dim xRg As Range
Dim xCell As Range
Dim A As Long
Dim B As Long
Dim C As Long
A = Worksheets("SOURCE DATA").UsedRange.Rows.Count
B = Worksheets("AS Brazil").UsedRange.Rows.Count
If B = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Apple").UsedRange) = 0 Then B = 0
End If
Set xRg = Worksheets("SOURCE DATA").Range("A2:A" & A)
On Error Resume Next
Application.ScreenUpdating = False
For A = 1 To xRg.Count
If CStr(xRg(A).Value) = "Apples" Then
xRg(A).EntireRow.Copy Destination:=Worksheets("Apple").Range("A" & B + 1)
B = B + 1
End If
Next
Application.ScreenUpdating = True


However, for this line- how can i make it look for two values, like if it says apples AND bananas?
xRg(A).EntireRow.Copy Destination:=Worksheets("Apple").Range("A" & B + 1)

 

[Joe4]

OK, if you are using Excel 365, there is an easier way that requires NO VBA!
Excel introduced a new Filter function, in which you enter a single formula and it returns all records meeting your criteria!
So all you would have to do is keep all your data on the one main tab, and then put one formula on each of the other tabs with the criteria in which to filter on.
It is dynamic, so as long as you enter a range in your formula large enough to handle new entries, you should never have to update any formulas after your initial set-up.
See: FILTER function - Microsoft Support

 

[frustrated_macro]

OK, if you are using Excel 365, there is an easier way that requires NO VBA!
Excel introduced a new Filter function, in which you enter a single formula and it returns all records meeting your criteria!
So all you would have to do is keep all your data on the one main tab, and then put one formula on each of the other tabs with the criteria in which to filter on.
It is dynamic, so as long as you enter a range in your formula large enough to handle new entries, you should never have to update any formulas after your initial set-up.
See: FILTER function - Microsoft Support

oh thats cool! would i still be able to insert that filter function into the macro though? its 28 different filters to do and if i have to paste in the formula every time, i might as well just filter and copy/paste manually
so like "in sheet "Source data" filter for all column A for Apple and paste all the rows into sheet "apples"

 

[Joe4]

would i still be able to insert that filter function into the macro though? its 28 different filters to do and if i have to paste in the formula every time, i might as well just filter and copy/paste manually

No need. You only need to set up the formula once for each tab and then NEVER again!
Even as the data on the main tab may change, the data will be dynamically updated on all the other tabs without you having to do anything.

The new FILTER function is a SPILL function. This means you only need to enter it ONCE per tab - you do NOT need to copy it to other rows.
If you put it in the upper left-most cell that you want (i.e. cell A2), the results will "SPILL" into all the other cells (it will automatically populate all the other rows and columns without you having to do a single thing!).

So you just enter the formula in one single cell on your tab, and you will get ALL the results meeting those conditions!
It is one of the coolest new functions in Excel, IMO.

 

[frustrated_macro]

No need. You only need to set up the formula once for each tab and then NEVER again!
Even as the data on the main tab may change, the data will be dynamically updated on all the other tabs without you having to do anything.

The new FILTER function is a SPILL function. This means you only need to enter it ONCE per tab - you do NOT need to copy it to other rows.
If you put it in the upper left-most cell that you want (i.e. cell A2), the results will "SPILL" into all the other cells (it will automatically populate all the other rows and columns without you having to do a single thing!).

So you just enter the formula in one single cell on your tab, and you will get ALL the results meeting those conditions!
It is one of the coolest new functions in Excel, IMO.

i see what you're saying. the only issue is that this is gonna be a new sheet every week. so i'd have to set it up every week. however, i did add that filter function into the vba to put it in every sheet
Range("A2").Formula = "=FILTER('SOURCE DATA'!A2:M10000,ISNUMBER(SEARCH(A1,'SOURCE DATA'!A2:A10000)))"

and that works, expect that once it copies, this is what my sheet says
=@FILTER('SOURCE DATA'!A2:M10000,ISNUMBER(SEARCH(A1,'SOURCE DATA'!A2:A10000)))
and the @ is not letting the filter spill down

 

[Joe4]

See if this variation works:

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

 

[frustrated_macro]

thanks joe! new problem

Sheets.Add.Name = "SCI"
Sheets("SCI").Select
Range("A1").Value = "Science Channel - Latin"
Range("A2").Formula2 = "=FILTER('SOURCE DATA'!A2:M10000,ISNUMBER(SEARCH(A1,'SOURCE DATA'!A2:A10000)))"
Sheets("SCI").Cells.Copy
Sheets("SCI").Cells.PasteSpecial Paste:=xlPasteValues
ActiveSheet.UsedRange.RemoveDuplicates Columns:=7, Header:=xlYes
ActiveSheet.UsedRange.AutoFilter Field:=13, Criteria1:="<>Complete"
ActiveSheet.Range("C2", "C10000").NumberFormat = "mm/dd/yyyy"
ActiveSheet.Range("L2", "L10000").NumberFormat = "mm/dd/yyyy"
Sheets("Source Data").Select
Range("A1").EntireRow.Copy
Sheets("SCI").Select
Range("A1").PasteSpecial
Range("A1").EntireColumn.Delete
ActiveSheet.UsedRange.EntireColumn.AutoFit


Sheets.Add.Name = "WHD"
Sheets("WHD").Select
Range("A1").Value = "Discovery World HD - Latin"
Range("A2").Formula2 = "=FILTER('SOURCE DATA'!A2:M10000,ISNUMBER(SEARCH(A1,'SOURCE DATA'!A2:A10000)))"
Sheets("WHD").Cells.Copy
Sheets("WHD").Cells.PasteSpecial Paste:=xlPasteValues
ActiveSheet.UsedRange.RemoveDuplicates Columns:=7, Header:=xlYes
ActiveSheet.UsedRange.AutoFilter Field:=13, Criteria1:="<>Complete"
ActiveSheet.Range("C2", "C10000").NumberFormat = "mm/dd/yyyy"
ActiveSheet.Range("L2", "L10000").NumberFormat = "mm/dd/yyyy"
Sheets("Source Data").Select
Range("A1").EntireRow.Copy
Sheets("WHD").Select
Range("A1").PasteSpecial
Range("A1").EntireColumn.Delete
ActiveSheet.UsedRange.EntireColumn.AutoFit

so im getting an error on the bold line because there was nothing on the SOURCE DATA that said science channel latin, which will happen
how do i get it to just skip over to the next block?