Move row to another sheet based on cell value

meinblitz

New Member
Joined
Feb 8, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Hi all

I know this topic has been covered in past threads so apologies for bringing it up once again - Just having trouble getting it to work.

I have a task register that lists varying statuses in Column E. When 'Complete' status is entered, I need the entire row (Range A to L) to move to Sheet2. Sheet2 is a duplicate of Sheet1 but specifically for collating all completed tasks. Once the move event has occurred, the empty row should be deleted from Sheet1.

I should mentioned that I am using another Worksheet_Change event in the same Sheet that fulfills a different purpose.

Image attached.

Thanks for your help
 

Attachments

  • Sheet1.jpg
    Sheet1.jpg
    84.7 KB · Views: 31

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Copy and paste your existing Worksheet_Change procedure into the reply box and we can modify it to do your 'Complete' items as well as what it currently does.
 
Upvote 0
Copy and paste your existing Worksheet_Change procedure into the reply box and we can modify it to do your 'Complete' items as well as what it currently does.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim controlRng, nRng As Range
    Set controlRng = Range("E:E")
    Set nRng = Intersect(controlRng, Target)

    If nRng Is Nothing Then Exit Sub

    If Target.Value = "Pending Approval" Then
        Target.Offset(0, 4).Value = Date

    ElseIf Target.Value = "Approved" Then
        Target.Offset(0, 5).Value = Date
    
    ElseIf Target.Value = "Assigned" Then
        Target.Offset(0, 6).Value = Date

    ElseIf Target.Value = "Complete" Then
        Target.Offset(0, 7).Value = Date
        
    Else
        'Do Something
    End If
    
End Sub

Thanks!!
 
Upvote 0
If the completed sheet name is not actually "Sheet2" you will need to make the change below.

Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim controlRng, nRng As Range
    Set controlRng = Range("E:E")
    Set nRng = Intersect(controlRng, Target)
    If nRng Is Nothing Then Exit Sub
    If Target.Value = "Pending Approval" Then
        Target.Offset(0, 4).Value = Date
    ElseIf Target.Value = "Approved" Then
        Target.Offset(0, 5).Value = Date
    ElseIf Target.Value = "Assigned" Then
        Target.Offset(0, 6).Value = Date
    ElseIf LCase(Target.Value) = "complete" Then
        Target.Offset(0, 7).Value = Date
        Cells(Target.Row, 1).Resize(, 12).Copy Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp)(2)
        Target.EntireRow.Delete
    Else
        'Do Something
    End If
End Sub
 
Upvote 0
If the completed sheet name is not actually "Sheet2" you will need to make the change below.

Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim controlRng, nRng As Range
    Set controlRng = Range("E:E")
    Set nRng = Intersect(controlRng, Target)
    If nRng Is Nothing Then Exit Sub
    If Target.Value = "Pending Approval" Then
        Target.Offset(0, 4).Value = Date
    ElseIf Target.Value = "Approved" Then
        Target.Offset(0, 5).Value = Date
    ElseIf Target.Value = "Assigned" Then
        Target.Offset(0, 6).Value = Date
    ElseIf LCase(Target.Value) = "complete" Then
        Target.Offset(0, 7).Value = Date
        Cells(Target.Row, 1).Resize(, 12).Copy Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp)(2)
        Target.EntireRow.Delete
    Else
        'Do Something
    End If
End Sub
Thank you so much - This definitely works however, it also returns;

Run-time error '13'
Type mismatch

When I debug, line 6 'If Target.Value = "Pending Approval" Then' is highlighted yellow

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim controlRng, nRng As Range
    Set controlRng = Range("E:E")
    Set nRng = Intersect(controlRng, Target)
    
    If nRng Is Nothing Then Exit Sub
    
    If Target.Value = "Pending Approval" Then
        Target.Offset(0, 4).Value = Date
        
    ElseIf Target.Value = "Approved" Then
        Target.Offset(0, 5).Value = Date
        
    ElseIf Target.Value = "Assigned" Then
        Target.Offset(0, 6).Value = Date
        
    ElseIf LCase(Target.Value) = "complete" Then
        Target.Offset(0, 7).Value = Date
        Cells(Target.Row, 1).Resize(, 12).Copy Sheets("Completed").Cells(Rows.Count, 1).End(xlUp)(2)
        Target.EntireRow.Delete
        
    Else
        'Do Something
    End If
    
End Sub
 
Upvote 0
That's odd, the two lines that I added wouldn't change anything above them. Change the word 'Value" to "Text" on all those If statements. Don't know why Excel does that but there is somethis about an If ElseIf statement that when you make a change to one of the conditions, Excel takes a different look at what you had before. I never get used to it.
 
Upvote 0
That's odd, the two lines that I added wouldn't change anything above them. Change the word 'Value" to "Text" on all those If statements. Don't know why Excel does that but there is somethis about an If ElseIf statement that when you make a change to one of the conditions, Excel takes a different look at what you had before. I never get used to it.
You're right, that worked it! I can't thank you enough, JLGWhiz.
 
Upvote 0
You're right, that worked it! I can't thank you enough, JLGWhiz.
Probably the easy way to do it is not use Value or Text on that side of the equal symbol and let VBA fgure out what the data type is on the right side of the equal symbol.. But if it is working, then that is what counts.
Regards, JLG
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

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