If cell contains copy row... but more complicated

HotLanta

Board Regular
Hi,

I started putting this one together but it became too complicated for me as I have two conditions...

On sheet "Sheet 1" if the cell in column I contains "L" then copy that row to sheet "LH" and then cycle through all the next rows until complete.
But also,
On sheet "Sheet 1" if the cell in column I contains "R" then copy that row to sheet "RH" and then cycle through all the next rows until complete.

I could do the one condition, but not two.

Thanks for any help!
 

Fluff

MrExcel MVP, Moderator
How about
Code:
Sub HotLanta()
    With Sheets("Sheet1")
        .Range("A1:I1").AutoFilter 9, "*L*"
        .AutoFilter.Range.Offset(1).EntireRow.Copy Sheets("LH").Range("A" & Rows.Count).End(xlUp).Offset(1)
        .Range("A1:I1").AutoFilter 9, "*R*"
        .AutoFilter.Range.Offset(1).EntireRow.Copy Sheets("RH").Range("A" & Rows.Count).End(xlUp).Offset(1)
        .AutoFilterMode = False
    End With
End Sub
 

jolivanes

Well-known Member
If you have a large range with data, autofilter (see Fluff's code) will be faster.
You can extend it as much as you want as long as the arrays have the same amount of data (i.e. each 12 items)
Code:
Sub Maybe()
Dim vArr, shArr, c As Range, i As Long
vArr = Array("L", "R")
shArr = Array("LH", "RH")
    For Each c In Range("I1:I" & Cells(Rows.Count, "I").End(xlUp).Row)
        For i = LBound(vArr) To UBound(vArr)
            If c.Value = vArr(i) Then c.EntireRow.Copy Sheets(shArr(i)).Cells(Rows.Count, "A").End(xlUp).Offset(1)
        Next i
    Next c
End Sub
 

HotLanta

Board Regular
Fluff and jolivanes,

Thank you both for your help, I tried Fluff's first and it has worked perfectly. (If it isn't broken - don't fix it). I will bookmark this and try jolivanes code if I run in to trouble later. Thanks again.
 

Some videos you may like

This Week's Hot Topics

Top