Move Row to another Worksheet Based on Multiple Conditions

Shruen

New Member
Joined
Feb 17, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello Everyone,

Attempting to create a script to move a row from one worksheet to another based on two separate cells. The first cell is a drop down list which will move only if showing as completed. The second cell is a date cell and needs to move when it is not blank and the first cell is listed as completed. Column C is the drop down list and Column F is the completed date section. Need the script to move the row when both conditions are true. Can get the row to move when just the drop down list is the condition (below) but any combination to include the date condition has failed. Might be overthinking it but would greatly appreciate any help. The current script works as follows:

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    Application.EnableEvents = False
    'If Cell that is edited is in column C and F and the value is completed then
    If Target.Column = 3 And Target.Value = "Completed" Then
        'Define last row on completed worksheet to know where to place the row of data
        LrowCompleted = Sheets("Archive").Cells(Rows.Count, "A").End(xlUp).Row
        'Copy and paste data
        Range("A" & Target.Row & ":BA" & Target.Row).Copy Sheets("Archive").Range("A" & LrowCompleted + 1)
        'Delete Row from Project List
        Range("A" & Target.Row & ":BA" & Target.Row).Delete xlShiftUp
    End If
    Application.EnableEvents = True
End Sub
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Try this:
For script to run Column F must have some value already entered before you enter "Completed" in column C
Not sure what this is all about but if it works that's great. Not sure what Range "BA" has to do with this:
"BA" & Target.Row).Copy Sheets("Archive").Range("A" & LrowCompleted + 1)"
Why "BA" ??

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    'Modified  6/8/2022  1:25:40 PM  EDT
    If Target.Column = 3 Then
    Application.EnableEvents = False
    If Target.Value = "Completed" And Target.Offset(, 3).Value <> "" Then
        LrowCompleted = Sheets("Archive").Cells(Rows.Count, "A").End(xlUp).Row
            Range("A" & Target.Row & ":BA" & Target.Row).Copy Sheets("Archive").Range("A" & LrowCompleted + 1)
        'Delete Row from Project List
        Range("A" & Target.Row & ":BA" & Target.Row).Delete xlShiftUp
    End If
    End If
    Application.EnableEvents = True
End Sub
 
Upvote 0
Solution
There are columns for notes for people hence why BA. Cannot go into great detail due to the nature of the document, but basically these are requirements which were deemed necessary. Also want to point out the script worked perfectly and when I reviewed what you provided I could see where I was making a mistake and it seems I was just overthinking it. Again thanks for the assistance, you are awesome.
 
Upvote 0
There are columns for notes for people hence why BA. Cannot go into great detail due to the nature of the document, but basically these are requirements which were deemed necessary. Also want to point out the script worked perfectly and when I reviewed what you provided I could see where I was making a mistake and it seems I was just overthinking it. Again thanks for the assistance, you are awesome.
Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top