Copy rows in a table based on criteria

Question

I have an excel table with values. I am trying to use VBA such that:

1. Rows where Col <> 0 gets copied to one worksheet
2. Rows where Col = 0 gets copied to another worksheet

The code, I have is from below, taken from here. But with the below, I only manage to copy 1, and not rows with criteria specified in 2.

Sub ExportData()

Dim rngJ As Range
Dim MySel As Range

Set rngJ = Range("O1", Range("O" & Rows.Count).End(xlUp))
Set wsNew = ThisWorkbook.Worksheets.Add

For Each cell In rngJ
    If cell.Value <> 0 Then
        If MySel Is Nothing Then
            Set MySel = cell.EntireRow
        Else
            Set MySel = Union(MySel, cell.EntireRow)
        End If
    End If
Next cell

If Not MySel Is Nothing Then MySel.Copy Destination:=wsNew.Range("A1")

End Sub

Answer 1

You could introduce an Elseif statement and a second range:

Sub ExportData()

Dim rngJ As Range
Dim MySel_1 As Range
Dim MySel_2 As Range

Set rngJ = Range("O1", Range("O" & Rows.Count).End(xlUp))
Set wsNew_1 = ThisWorkbook.Worksheets.Add
Set wsNew_2 = ThisWorkbook.Worksheets.Add

For Each cell In rngJ
    If cell.Value <> 0 Then
        If MySel_1 Is Nothing Then
            Set MySel_1 = cell.EntireRow
        Else
            Set MySel_1 = Union(MySel_1, cell.EntireRow)
        End If
    ElseIf cell.Value = 0 Then
        If MySel_2 Is Nothing Then
            Set MySel_2 = cell.EntireRow
        Else
            Set MySel_2 = Union(MySel_2, cell.EntireRow)
        End If
    End If
Next cell

If Not MySel_1 Is Nothing And Not MySel_2 Is Nothing Then
    MySel_1.Copy Destination:=wsNew_1.Range("A1")
    MySel_2.Copy Destination:=wsNew_2.Range("A1")
End If

End Sub