VBA Cut/paste based on cell value

[jashotze]

Hey All,
First time poster, as I am having a real crap time getting this code to work... I found a segment on one of these forums and need to amend it slightly... I need to detail the row that is copied and subsequently pasted.

I have tried every form of syntax I know how. Even paid 20 bucks an hour for an online Code Mentor and they could get it to work. I don't know if it needs to be just scrapped and started over or what. I have tried probably 30 different variations to accomplish this, even recorded the entire thing of searching, cutting and deleting. No Luck.

From the marginal amount I know this one seems to have some extra stuff (A1.Select) but when I remove it the I get object errors. But if the line to delete can be found I won't care haha.

This code works, it just doesn't delete.... Please help!!!!

[COLOR=#000000][FONT=Helvetica]Sub Completed_Rows()[/FONT][/COLOR]

[COLOR=#000000][FONT=Helvetica]'Unprotect a worksheet with a password[/FONT][/COLOR]
[COLOR=#000000][FONT=Helvetica]Sheets("Hi-Pri").Unprotect Password:="ov22gov"[/FONT][/COLOR]
[COLOR=#000000][FONT=Helvetica]Sheets("Completed").Unprotect Password:="ov22gov"[/FONT][/COLOR]

[COLOR=#000000][FONT=Helvetica]Dim lr As Long, lr2 As Long, r As Long[/FONT][/COLOR]
[COLOR=#000000][FONT=Helvetica]lr = Sheets("Hi-Pri").Cells(Rows.Count, "A").End(xlUp).Row[/FONT][/COLOR]
[COLOR=#000000][FONT=Helvetica]lr2 = Sheets("Completed").Cells(Rows.Count, "A").End(xlUp).Row[/FONT][/COLOR]
[COLOR=#000000][FONT=Helvetica]For r = lr To 2 Step -1[/FONT][/COLOR]
[COLOR=#000000][FONT=Helvetica]   If Range("T" & r).Value = "Complete" Then[/FONT][/COLOR]
[COLOR=#000000][FONT=Helvetica]       Rows(r).Copy Destination:=Sheets("Completed").Range("A" & lr2 + 1)[/FONT][/COLOR]
[COLOR=#000000][FONT=Helvetica]       lr2 = Sheets("Completed").Cells(Rows.Count, "A").End(xlUp).Row[/FONT][/COLOR]
[COLOR=#000000][FONT=Helvetica]   End If[/FONT][/COLOR]
[COLOR=#000000][FONT=Helvetica]   Range("A1").Select[/FONT][/COLOR]
[COLOR=#000000][FONT=Helvetica]Next r[/FONT][/COLOR]

[COLOR=#000000][FONT=Helvetica]'Protect worksheet with a password[/FONT][/COLOR]
[COLOR=#000000][FONT=Helvetica]Sheets("Hi-Pri").Protect Password:="ov22gov"[/FONT][/COLOR]
[COLOR=#000000][FONT=Helvetica]'ActiveWorkbook.Save[/FONT][/COLOR]
[COLOR=#000000][FONT=Helvetica]Sheets("Completed").Protect Password:="ov22gov"[/FONT][/COLOR]

 

[jashotze]

It Worked!!! You're a lifesaver! I didn't even think of that. But I have had a few instances where for some reason it wasn't running and I had to use the immediate window to turn it back on so the macro would work.. Thanks again!!

 

[Fluff]

Glad to help & thanks for the feedback