Need help moving entire rows to another worksheet.

[PorscheGT2]

Ok, here's the code:

Application.ScreenUpdating = False
Columns("a:a&quot.Select
PurchID = "221"
If TypeName(Cells.Find(PurchID)) <> &quot;Range&quot; Then
MsgBox (&quot;Purchaser ID not found!&quot
Range(&quot;a1&quot.Select
Else: Cells.Find(PurchID).Activate
ActiveCell.EntireRow.Select
Selection.Cut
mysel = Selection
Sheets(2).Activate
Columns(&quot;a:a&quot.Select
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Activate
Cells.Next.Activate
ActiveCell.EntireRow.Select
ActiveSheet.Paste

End If

What it does is that it finds a record (Purchaser ID) in column A, and if it finds a match, it highlights (selects) the entire row corresponding to that record and moves it to another worksheet. My problem is, how do you determine the next empty cell in column A in the other worksheet and paste the record there? Sorry about the clunky code, I'm just starting and I'm learning these on my own. Thanks for any help you can give.

 

[SkipVought]

porche,

Check this out

faq707-4105 How Can I Make My Code Run Faster?

    Application.ScreenUpdating = False
    PurchID = "221"
    Set rng = Range("A:A").Find(PurchID)
    If rng Is Nothing Then
        MsgBox ("Purchaser ID not found!")
        Range("a1").Select
    Else
        rng.Cut (Sheets(2).Cells(Sheets(2).Cells(1, 1).CurrentRegion.Rows.Count + 1, 1))
    End If
    Application.ScreenUpdating = True

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

Sorry, I missed the entire row...

        rng.EntireRow.Cut _
        (Sheets(2).Cells(Sheets(2).Cells(1, 1).CurrentRegion.Rows.Count + 1, 1))

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PorscheGT2]

Hello Skip,

Thanks for the tip. Although the code only does half of what I wanted it to do. I actually wanted to paste the entire row to sheet 2. The code only cuts and paste the purchaser id found. I have yet to understand the arguments in your code too . Maybe you can explain what the arguments mean so I can make the appropriate changes.

Thanks,

Marvin

 

[SkipVought]

Yes, I missed the EntireRow property.

I also failed to mention that you need at least one heading in A1 on Sbeet2.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PorscheGT2]

Ok, cool. That was quick! Thanks again. BTW, Skip, is it possible to delete that entire row where the original data came from?

Thanks,

Marvin

 

[SkipVought]

Sub testt()
    Application.ScreenUpdating = False
    PurchID = "221"
    Set rng = Range("A:A").Find(PurchID)
    If rng Is Nothing Then
        MsgBox ("Purchaser ID not found!")
        Range("a1").Select
    Else
        With rng.EntireRow
            .Copy _
            (Sheets(2).Cells(Sheets(2).Cells(1, 1).CurrentRegion.Rows.Count + 1, 1))
            .Delete shift:=xlUp
        End With
    End If
    Application.ScreenUpdating = True

End Sub

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PorscheGT2]

Thanks Skip, I gave you a star! That was very helpful.