Renaming Subs for multiple usage, Moving Rows and in VBA

Question

I am brand new to coding.

I am trying to use the code to move rows to different sheets and to move completed rows to a different work.

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.

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 following instruction from another forum. 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_ChangeCOMPLETE(ByVal Target As Range)


       Dim destWbk As String
       Dim wbk As Workbook
       Dim rngDestCOMPLETE 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("rngTrigger")) 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_Change3(ByVal Target As Range)
 Dim rngDest3 As Range
 Set rngDest3 = 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

You can't rename these Subs, what you could do is write all three into a single Sub and then use some Select Case or IF statements to do the three different things you want under a single Sub routine. — Xabier

Xabier Xabier · Accepted Answer · 2018-05-08T12:43:05

As I mentioned on the comment you cannot rename these Subs, but you can do something like below:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDest As Range, rngDest2 As Range, rngDest3 As Range
Dim destWbk As String
Dim wbk As Workbook
     If UCase(Target.Value) = "PARTIAL HOLD" Then
        Set rngDest = Sheet3.Range("A5:R5")
        If Not Intersect(Target, Sheet1.Range("M5:M290")) Is Nothing Then
            Application.EnableEvents = False
            Target.EntireRow.Cut
            rngDest.Insert Shift:=xlDown
            Target.EntireRow.Delete
            Application.EnableEvents = True
        End If
    ElseIf UCase(Target.Value) = "IN PROGRESS" Then
        Set rngDest3 = Sheet1.Range("A5:S5")
        If Not Intersect(Target, Sheet3.Range("M5:M290")) Is Nothing Then
            Application.EnableEvents = False
            Target.EntireRow.Cut
            rngDest3.Insert Shift:=xlDown
            Target.EntireRow.Delete
            Application.EnableEvents = True
        End If
    ElseIf UCase(Target.Value) = "COMPLETED" Then
        destWbk = ThisWorkbook.Names("Completed.xlsx").RefersTo
        destWbk = Replace(destWbk, "=" & Chr(34), "")
        destWbk = Replace(destWbk, Chr(34), "")
        Set wbk = Application.Workbooks(destWbk)
        Set rngDest2 = wbk.Range("A1:S1")
        If Not Intersect(Target, Sheet1.Range("rngTrigger")) Is Nothing Then
             Application.EnableEvents = False
             Target.EntireRow.Cut
             rngDest2.Insert Shift:=xlDown
             Target.EntireRow.Delete
             Application.EnableEvents = True
        End If
    End If
End Sub

Renaming Subs for multiple usage, Moving Rows and in VBA

1 Answers