Finding Specific Rows and pasting to new worksheet

[PVANS]

Good morning,

I have to worksheets in my workbook, "uncleanded" and "reportable"

In the uncleaned worksheet I have a number of transactions. What I am
trying to do is find, copy and paste specific transactions into the second
worksheet.

Current code:
Dim c As Range
Dim SrchRng

Sheets("Uncleaned").Select
Set SrchRng = ActiveSheet.Range("H1", ActiveSheet.Range("A1000").End(xlUp))
Do
Set c = SrchRng.Find("Coke", LookIn:=xlValues)
If Not c Is Nothing Then c.EntireRow.Cut
Sheets("Reportable").Select
ActiveSheet.Paste
Loop While Not c Is Nothing


Unforetunately, the code currently has two major flaws:
1. it simply copies into row1 on worksheet "reportable"
2. it crashes at the end.
"Run-time error '1004': Paste method of Worksheet class failed"

I have been trying, without success, to find solutions for these two issues
(which I am sue are quite simple mistakes). Please can someone help me with
this.

thanks in advance, I really do appreciate the help

Paul

 

[Mike H]

Hi,

try this alternative approach

Sub Try_This()
Dim c As Range
Dim SrchRng As Range
Set SrchRng = Sheets("Uncleaned").Range("H1", _
Sheets("Uncleaned").Range("A1000").End(xlUp))
For Each c In SrchRng
If UCase(c.Value) = "COKE" Then
lastrow = Sheets("Reportable").Cells(Cells.Rows.Count, _
"A").End(xlUp).Row + 1
c.EntireRow.Copy Destination:=Sheets("Reportable").Range("A" _
& lastrow)
c.EntireRow.Delete
End If
Next
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[PVANS]

Thanks Mike,

Works like a charm

Regards,

Hi,

try this alternative approach

Sub Try_This()
Dim c As Range
Dim SrchRng As Range
Set SrchRng = Sheets("Uncleaned").Range("H1", _
Sheets("Uncleaned").Range("A1000").End(xlUp))
For Each c In SrchRng
If UCase(c.Value) = "COKE" Then
lastrow = Sheets("Reportable").Cells(Cells.Rows.Count, _
"A").End(xlUp).Row + 1
c.EntireRow.Copy Destination:=Sheets("Reportable").Range("A" _
& lastrow)
c.EntireRow.Delete
End If
Next
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Mike H]

Glad I could help and thanks for the feedback
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.