Cut and Paste macro stopped working...

[Kingwi11y]

Hi I adapted a macro to help me sort data - it searches for a value in the rows and any rows in that worksheet, it copys them to another worksheet called "results" and then deletes the original row:

Sub CutandPasteRows()
Dim rng As Range
Dim rng2 As Range
Dim cl As Range
Dim str As String

str = InputBox("Enter search text", "Find & Copy")
If str = "" Then Exit Sub
Set rng = ActiveSheet.UsedRange
For Each cl In rng
If cl.Value Like "*" & str & "*" Then
If rng2 Is Nothing Then
Set rng2 = cl
Else: Set rng2 = Union(rng2, cl)
End If
End If
Next cl
If rng2 Is Nothing Then
MsgBox ("None found")
Exit Sub
End If
rng2.EntireRow.Copy Worksheets("Results").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
rng2.EntireRow.Delete

End Sub


This has worked well until today using the same original data set but today I get the message: That command cannot be used on multiple selections

and it highlights the line:
rng2.EntireRow.Copy Worksheets("Results").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

Please can you let me know any ideas why it would suddenly stop working or how to fix it?

Thanks

Will

> I've tried copying the data to a new workbook, pasting values only, importing data to a new workbook - no joy...
> If i take a small section of the data and try the macro - it works - even when there is more than one incidence of the search term
>Another error it now sometimes comes up with is "copy method of Range class failed" and highlights the same line

>Is there more information I should supply? - Its Excel 2003, used on a work computer

 

[Brianwarnock]

I don't get here often these days and I suppose that this has been resolved by now but in case it hasn't I think that the probable cause of the problem is the string appearing in more than one Cell in a Row, and if it hadn't failed deleting one row twice would have caused problems.
As I don't know the full scenario, eg Data or what you are searching for,can the string be in any column, is the order on the Results sheet important, I cannot suggest the best solution.

Brian

 

[Kingwi11y]

Hi Brian

Thanks for this - this is a strong possibility now i think about it.

When I get access to the file again ill test this and if it's resolved ill post back.

Thanks
Will

 

[Kingwi11y]

This was the problem - same search term was in multiple columns in a few of the rows

Thanks for this - resolved