Excel Forum

Code: Select all

Private Sub CommandButton6_Click()
Dim r As Range, rAll As Range
    With Sheets("Input")
        Set rAll = .Range("G1", .Range("G" & Rows.Count).End(xlUp))
    End With
    For Each r In rAll
        Select Case r.Value
            Case "L23L23"
                Sheets("Input").Range("N" & Rows.Count).End(xlUp). _
                    Offset(1, 0).EntireRow = r.EntireRow.Value
            Case "L23U21"
                Sheets("Input").Range("R" & Rows.Count).End(xlUp). _
                    Offset(1, 0).EntireRow = r.EntireRow.Value
            Case "L23U08"
                Sheets("Input").Range("V" & Rows.Count).End(xlUp) _
                    .Offset(1, 0).EntireRow = r.EntireRow.Value
            Case "L23U09"
                Sheets("Input").Range("Z" & Rows.Count).End(xlUp) _
                    .Offset(1, 0).EntireRow = r.EntireRow.Value
            
            End Select
    Next r

End Sub

Code: Select all

For Each r In rAll
    Select Case r.Value
        Case "L23L23"
            Sheets("Input").Range("N" & Rows.Count).End(xlUp). _
                Offset(1, 0).Resize(1, 4).Value = r.Resize(1, 4).Value
        Case "L21U21"
            Sheets("Input").Range("R" & Rows.Count).End(xlUp). _
                Offset(1, 0).Resize(1, 4).Value = r.Resize(1, 4).Value
        Case "L21U08"
            Sheets("Input").Range("V" & Rows.Count).End(xlUp). _
                Offset(1, 0).Resize(1, 4).Value = r.Resize(1, 4).Value
    End Select
Next r