copy column if previous column = A

[luci_gg]

I have the following code to copy column E from Sheet 1 to Column G of Sheet 2.
I only want to copy the cells in column E if Column C = A

Sub Copycol()
Sheets("Sheet1").Select
Sheet2.Activate

If Range("c1:c7").Cells.Value = "A" Then
Range("e1:e7").Copy
Sheets("Sheet2").Select
Range("G3:G10").Select
ActiveSheet.Paste
End If

End Sub

Type missmatch error on the If line.

Thanks!!!

 

[lenze]

Perhaps like this??

Sub CopyA()
Dim cl As Range
 For Each cl In Sheet1.Range("C1:C7")
    If cl = "A" Then Cells(cl.Row, "E").Copy Sheet2.Cells(cl.Row + 2, "G")
Next cl
End Sub

Note there is no selection or activation of sheets involved!!!

lenze

 

[luci_gg]

Yeah that does what I wanted. Thanks a lot
I just need to copy the data withouth leaving blank cells... ie.
copy the data in the last row available of colum G.

Sub CopyA()
Dim cl As Range
For Each cl In Sheet1.Range("C1:C7")
If cl = "F2000" Then Cells(cl.Row, "E").Copy Sheet2.Cells(cl.LastRow, "G")
Next cl
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

I tried this but I got the error 438, object doesn't support this property or method...

 

[lenze]

Sub CopyA()
Dim cl As Range
For Each cl In Sheet1.Range("C1:C7")
LR = Sheet2.Cells(Rows.Count, "G").End(xlUp).Row
If cl = "A" Then Sheet1.Cells(cl.Row, "E").Copy Sheet2.Cells(LR + 1, "G")
Next cl
End Sub

lenze

 

[luci_gg]

Perfect, thank you very much