Cutting and pasting between worksheets based on a column

Hi,

I am trying to filter a mass amount of exported data into separate excel worksheets to look at in more detail but wanted to split them depending on what is in one column.

I have the following code atm, which I have been using on a different speadsheet but it just is not working within this one.

Any help be appreciated, as I am still learning vba, I can't see the problems very easily.

Private Sub Worksheet_Change(ByVal Target As Range)

'Shift of Non Conformances


Application.EnableEvents = False
For Each cell In Target
    With cell
        Select Case cell.Column
                      
            Case 17     'column Q changed

                    'INC
            If LCase(.Value) = "Non Conformance" Then
            
                    .EntireRow.Copy Worksheets("INC").Cells(Rows.Count, 1).End(xlUp)(2, 1)
                    .EntireRow.Delete
              
                    'OOS
            ElseIf .Value = "OOS" Then
                    .EntireRow.Copy Worksheets("OOS").Cells(Rows.Count, 1).End(xlUp)(2, 1)
                    .EntireRow.Delete
                    
                    
              End If
            
                          
        End Select
    End With
Next cell
Application.EnableEvents = True

End Sub

Maybe:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("Q:Q")) Is Nothing Then
    Select Case Target.Value
        Case Is = "Non Conformance"
            Rows(Target.Row).Copy Worksheets("INC").Cells(Rows.Count, 1).End(xlUp)(2, 1)
            Rows(Target.Row).Delete
        Case Is = "OOS"
            Rows(Target.Row).Copy Worksheets("OOS").Cells(Rows.Count, 1).End(xlUp)(2, 1)
            Rows(Target.Row).Delete
    End Select
End If
End Sub

Hi John,

thanks, but this seems not to work either...but thanks for trying, any other suggestions or areas that would be the issue?