Problem with macro intended to copy cells to a different worksheet based on cell value

Hi there,

Below is part of a macro that I've partly written and partly based off other threads/advice provided here.

It's intention is to identify which row has one of 18 possible values in it's 'H' column and then copy colums A:H of that row into the range A86:H86 in a seperate spreadsheet. A subsequent part of the macro then copies A86:H86 to another part of the spreadsheet, but that is tested and working fine, so I've left it out for the sake of brevity.

The issue I am having is with the line "Then Range(Intersect(Rows(i), Range("A:H"))).Copy Sheets("Lists & Formulas").Range("A86:H86"). When the macro runs it returns an error 'Run-time error '1004': Method 'Range' of object '_Global' failed.

Dim i As Long, nrow As Long
    With Worksheets("Work on Hand")
            Application.Goto Reference:="name1"
            nrow = .Range("A" & ActiveCell.Row).End(xlUp).Row
            For i = 4 To nrow
                If .Range("H" & i).Value = "value1" _
                Or .Range("H" & i).Value = "value2" _
                Or .Range("H" & i).Value = "value3" _
                Or .Range("H" & i).Value = "value4" _
                Or .Range("H" & i).Value = "value5" _
                Or .Range("H" & i).Value = "value6" _
                Or .Range("H" & i).Value = "value7" _
                Or .Range("H" & i).Value = "value8" _
                Or .Range("H" & i).Value = "value9" _
                Or .Range("H" & i).Value = "value10" _
                Or .Range("H" & i).Value = "value11" _
                Or .Range("H" & i).Value = "value12" _
                Or .Range("H" & i).Value = "value13" _
                Or .Range("H" & i).Value = "value14" _
                Or .Range("H" & i).Value = "value15" _
                Or .Range("H" & i).Value = "value16" _
                Or .Range("H" & i).Value = "value17" _
                Or .Range("H" & i).Value = "value18" _
                Then Range(Intersect(Rows(i), Range("A:H"))).Copy Sheets("Lists & Formulas").Range("A86:H86")
                Range("A" & i).EntireRow.Delete
            Next i
        End With

You can only use "Then" in an IF statement, like: If 1 < 5 Then Your code does not do that. Try removing the Then and see what happens.

Originally Posted by Crangiopharengoma

It's intention is to identify which row has one of 18 possible values in it's 'H' column and then copy colums A:H of that row into the range A86:H86 in a seperate spreadsheet.

Try something like this...

    Dim i As Long, nrow As Long, arrVals As Variant
    arrVals = Array("value1", "value2", "value3", "value4", "value5", _
                    "value6", "value7", "value8", "value9", "value10", _
                    "value11", "value12", "value13", "value14", "value15", _
                    "value16", "value17", "value18")
    With Worksheets("Work on Hand")
        Application.Goto Reference:="name1"
        nrow = .Range("A" & ActiveCell.Row).End(xlUp).Row
        For i = 4 To nrow
            If IsNumeric(Application.Match(.Range("H" & i), arrVals, 0)) Then
                .Rows(i).Range("A1:H1").Copy Sheets("Lists & Formulas").Range("A86:H86")
                .Rows(i).Delete
                Exit For
            End If
        Next i
    End With