To copy an entire row to an other sheet if a cell meets a criteria

[jensi358]

Hi,

I'm a beginner and I don't know what I'm doing, but I would need some help with coding a macro which copies a whole row if a specific cell in this row meets a criteria.

So, in Column H I have values ">12 months" and "<12 months" to get information of the length of a contract. If this cell takes value "<12 months" I want excel to copy the entire row to Sheet 2. I have googled a bit but this is what I've done this far:

Option Explicit
Private Sub Worksheet_change(ByVal Target As Range)
Dim nxtRow As Integer, b As Boolean
Dim H2 As Range
If Target.Column = 8 Then
If Target.Value = "<12 months" Then
b = True
Else
b = False
End If
End If

If b Then
nxtRow = Sheets(2).Range("H" & Rows.Count).End(xlUp)
Target.EntireRow.Copy_
Destination = Sheets(2).Range("A" & nxtRow)
End If
End Sub

But something is wrong, Excel doesn't read this as an macro, every time I try to play the macro Excel gives me an opinion to write it again? The problem seems to be in the first rows "Option Explicit
Private Sub Worksheet_change(ByVal Target As Range)"

Can somebody help me to write the code that it works? Can you see what is wrong with it? I studied VBA two weeks at Uni and it didn't give me much more than really basics.

[AB33]

Please use code tags with your code.
The values you are looking for may be are numbers.
Try one of these lines

Please Login or Register  to view this content.