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
SHEET 3
SHEET 1
Code:
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 SubSHEET 3
Code:
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
Last edited by a moderator:
