Duplicate Subroutine names for moving rows to different sheets and workbook

[PerryK]

Hello everyone, I am brand new to coding, and I am trying to use the code to move rows to different sheets and to move completed rows to a different work and I am having trouble that the Sub Worksheet_Change is being seen as ambiguous name and doesn't work when I try to change the name to something like Worksheet_ChangeCOMPLETE or WorkSheet_Change3, also rngDest has the same issue in all 3 codes, below is the codes that I am trying to use. What my plan is that I want completed orders (rows) to move to a new workbook in which I have named "COMPLETED" when a command button is pushed which triggers a Macro to insert the word "COMPLETE" in column 13 (M). This new workbook was formerly my sheet 2 but I made it a new workbook and saved it as COMPLETED.xlxs I also need rows to move to sheet 3 when "PARTIAL HOLD" inserted in column 13 via a different command button and then returned to sheet one when the command button on sheet 3 "RESUME" is clicked. All workbooks and worksheets have all the same columns and spacing, I just can't get the codes to work when I rename them. The first set of codes I am posting are for moving rows from sheet 1 to sheet 3 when the command button is pressed, followed by the code to move rows to the new workbook these codes are in Sheet 1 under VBA project, not a module. The third is on sheet 3 to move rows back to sheet 1 once HOLD is complete. Thank you in advance for your help.


SHEET 1

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDest As Range
Set rngDest = Sheet3.Range("A5:R5")
If Not Intersect(Target, Sheet1.Range("M5:M290")) Is Nothing Then
If UCase(Target) = "PARTIAL HOLD" Then
Application.EnableEvents = False
Target.EntireRow.Select
Selection.Cut
rngDest.Insert shift:=xlDown
Selection.Delete
Application.EnableEvents = True
End If
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim destWbk As String
Dim wbk As Workbook
Dim rngDest As Range
destWbk = ThisWorkbook.names("COMPLETED.xlsx").RefersTo
destWbk = Replace(destWbk, "=" & Chr(34), "")
destWbk = Replace(destWbk, Chr(34), "")
Set wbk = Application.Workbooks(destWbk)
Set rngDest = wbk.names("A1:S1").RefersToRange
If Not Intersect(Target, Sheet1.Range("COMPLETE")) Is Nothing Then
If UCase(Target) = "COMPLETED" Then
Application.EnableEvents = False
Target.EntireRow.Select
Selection.Cut
rngDest.Insert shift:=xlDown
Selection.Delete

Application.EnableEvents = True
End If
End If
End Sub

SHEET 3

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDest As Range
Set rngDest = Sheet1.Range("A5:S5")
If Not Intersect(Target, Sheet3.Range("M5:M290")) Is Nothing Then
If UCase(Target) = "IN PROGRESS" Then
Application.EnableEvents = False
Target.EntireRow.Select
Selection.Cut
rngDest.Insert shift:=xlDown
Selection.Delete
Application.EnableEvents = True
End If
End If
End Sub

 

[PerryK]

Thank you very much again for all the effort you've put in this and patience you've had with me. I truly appreciate it. Most of this work. The only thing that is not working is the COMPLETE row move to sheet 2. I'm not getting an error just nothing is happening. PARTIAL HOLD goes to sheet 3 perfectly and PROGRESSING comes back to sheet 1 perfectly, but COMPLETE does nothing. I tried changing the IF to and ElseIf but that causes an compile error. I'll continue playing with it. Thank you again for all your assistance.

 

[PerryK]

Ignore my last comment, I decided to test it directly on my primary workbook and I am getting run time error 9 subscript out of range. When clicking debug it highlights either line 7 or line 11 depending on if I was testing the complete (line 7) or the hold (line 11) macro.

 

[PerryK]

OK So I started all over this morning and rebuilt from a new template of the general lay out. I put in the new code and I am getting Run Time Error 9 subscript out of range. I get this error and it highlights line 7 when trying to do the COMPLETE process and line 11 when trying to do to the HOLD

 

[PerryK]

Never mind, I fixed it. I changed Sheets("Sheet2').Range to Sheet2.range for all respective codes and now what you gave me works perfectly. Thank you very much for all the work you've put in and the patience you've had dealing with a newb like myself.

 

[JLGWhiz]

Never mind, I fixed it. I changed Sheets("Sheet2').Range to Sheet2.range for all respective codes and now what you gave me works perfectly. Thank you very much for all the work you've put in and the patience you've had dealing with a newb like myself.

Sheet names can be tricky in Excel if using the numbers. Sheet1 (Excel code name), Sheets("Sheet1") (Sheet tab name) and Sheets(1) (Sheet index number) can be three different sheets in the same workbook. Anytime you get the subsript out of range error, look for misspelled object names, wronge names referenced, wrong case and spaces leading, trailing or between in error. Sheet names must be an exact match between the code and the worksheet.

Regards, JLG