Sorting specific data from one sheet to another

I'm copying data from a sheet called "All Records" and pasting the results in
another sheet in the same workbook called "GESACard". I want to copy any
rows that have "4-$" Or "CNO-$" in column 1 and "GESA CC" in column 2. This
code is not working for me, it is something to do with the "4-$" Or "CNO-$" I
think. Any help you can provide is appreciated. Thank you

Sub CopyDataGESACard()

' Create GESA CC Matching Report

Dim rng As Range, cell As Range
Dim i As Long, sh As Worksheet
With Worksheets("All Records")
Set rng = .Range(.Cells(1, 1), _
..Cells(Rows.Count, 1).End(xlUp))
End With
i = 1

Set sh = Worksheets("GESACard")
For Each cell In rng
If UCase(Trim(cell.Value)) = "4-$" Or "CNO-$" And _
UCase(Trim(cell.Offset( _
0, 1).Value)) = "GESA CC" Then
cell.EntireRow.Copy sh.Cells(i, 1)
i = i + 1
End If
Next
End Sub

Maybe this will help.

Sub GESACC()
Dim rng As Range, cell As Range
Dim i As Long, sh As Worksheet

With Worksheets("All Records")
Set rng = .Range(.Cells(1, 1), _
.Cells(Rows.Count, 1).End(xlUp))
End With
i = 1

Set sh = Worksheets("GESACard")
For Each cell In rng
If UCase(Trim(cell.Value)) = "4-$" Or _
UCase(Trim(cell.Value)) = "CNO-$" Then
If UCase(Trim(cell.Offset(0, 1).Value)) = _
"GESA CC" Then
cell.EntireRow.Copy sh.Cells(i, 1)
i = i + 1
End If
End If
Next
End Sub
--
Mike Q.


"JOUIOUI" wrote:

> I'm copying data from a sheet called "All Records" and pasting the results in
> another sheet in the same workbook called "GESACard". I want to copy any
> rows that have "4-$" Or "CNO-$" in column 1 and "GESA CC" in column 2. This
> code is not working for me, it is something to do with the "4-$" Or "CNO-$" I
> think. Any help you can provide is appreciated. Thank you
>
> Sub CopyDataGESACard()
>
> ' Create GESA CC Matching Report
>
> Dim rng As Range, cell As Range
> Dim i As Long, sh As Worksheet
> With Worksheets("All Records")
> Set rng = .Range(.Cells(1, 1), _
> .Cells(Rows.Count, 1).End(xlUp))
> End With
> i = 1
>
> Set sh = Worksheets("GESACard")
> For Each cell In rng
> If UCase(Trim(cell.Value)) = "4-$" Or "CNO-$" And _
> UCase(Trim(cell.Offset( _
> 0, 1).Value)) = "GESA CC" Then
> cell.EntireRow.Copy sh.Cells(i, 1)
> i = i + 1
> End If
> Next
> End Sub