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
Bookmarks