VBA to move row based on one criteria onto another sheet

Matt Young

New Member
Joined
Nov 2, 2018
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hello there,
I realise this question has probably been asked (many times) before but I am really struggling to come up/ find anything that I fulfills my needs.
Basically I just need to be able to push a button that moves (not copies) an entire row (A:Q) onto another sheet (called "Closed") when the status in Column "N" is changed to closed, I don't want it be moved to new sheet each time but just keep adding rows to the "Closed" sheet . I have had varying levels of success but I have not got it right. Unfortunately I haven't got any of the code from these attempts. If any one could help me out It would be much appreciated.
 

Attachments

  • Screenshot 2020-09-14 140958.png
    Screenshot 2020-09-14 140958.png
    67.3 KB · Views: 17

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

does this work as expected?

VBA Code:
Sub MOVE_TO_CLOSED()
    Application.ScreenUpdating = False
    With Sheets("Reported Issues")
        For MY_ROWS = .Range("A" & .Rows.Count).End(xlUp).Row To 2 Step -1
            If UCase(.Range("N" & MY_ROWS).Value) = "CLOSED" Then
                .Rows(MY_ROWS).Copy
                Sheets("CLOSED").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
                .Rows(MY_ROWS).Delete
            End If
        Next MY_ROWS
    End With
    Application.ScreenUpdating = True
End Sub
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,989
Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter "Closed" in column N and press the RETURN key.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("N:N")) Is Nothing Then Exit Sub
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    If Target = "Closed" Then
        Target.EntireRow.Copy Sheets("Closed").Cells(Sheets("Closed").Rows.Count, "A").End(xlUp).Offset(1)
        Target.EntireRow.Delete
    End If
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,990
Messages
5,575,397
Members
412,661
Latest member
joelgibney1
Top