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]

 

[Fluff]

Hi & welcome to the board.
The reason it's not deleting anything, is because you haven't told it to
Try

Rows(r).Copy Destination:=Sheets("Completed").Range("A" & lr2 + 1)
Rows(r).Delete
lr2 = Sheets("Completed").Cells(Rows.Count, "A").End(xlUp).Row

 

[jashotze]

Haha I guess I deserved that.

Unfortunately I’ve tried that exact line you posted and nothing...

 

[Fluff]

Do you get any error messages?

 

[jashotze]

Do you get any error messages?

I did, sorry about that. I get run-time error 1004. Delete method of Range class failed.

 

[Fluff]

Do you have any merged cells?

 

[jashotze]

Do you have any merged cells?

Only in the header of both sheets. None of the cells referenced or being used by VBA is merged. I try to avoid them.

 

[Fluff]

In that case I can't think why you'd get that particular error.
Can you delete one of those rows manually?

 

[jashotze]

I just tested it and if I select the row and delete it works fine. No issues..

Would a private sub I have running on the worksheet have anything to do with it? Its a timestamp in a column based on data in a different cell and works flawlessly. I do notice that data entry into any cell causes excel to think for just a second as I presume its running the script to see if the private subs parameters are met. The manual deletion worked fine even with the private sub working.

 

[Fluff]

If it's an Event code try

Sub Completed_Rows()

'Unprotect a worksheet with a password
   Sheets("Hi-Pri").Unprotect Password:="ov22gov"
   Sheets("Completed").Unprotect Password:="ov22gov"
   
   Dim lr As Long, lr2 As Long, r As Long
   lr = Sheets("Hi-Pri").Cells(Rows.Count, "A").End(xlUp).Row
   lr2 = Sheets("Completed").Cells(Rows.Count, "A").End(xlUp).Row
Application.EnableEvents = False
   For r = lr To 2 Step -1
      If Range("T" & r).Value = "Complete" Then
          Rows(r).Copy Destination:=Sheets("Completed").Range("A" & lr2 + 1)
          Rows(r).Delete
          lr2 = Sheets("Completed").Cells(Rows.Count, "A").End(xlUp).Row
      End If
   Next r
   Range("A1").Select
Application.EnableEvents = True
   
   'Protect worksheet with a password
   Sheets("Hi-Pri").Protect Password:="ov22gov"
   'ActiveWorkbook.Save
   Sheets("Completed").Protect Password:="ov22gov"
End Sub