Forum Discussion

Chris_Munday's avatar
Chris_Munday
Copper Contributor
May 26, 2022

VBA: Moving a row to new sheet based on list item

Hello, I have a workbook with two sheets: 'Live or Hold' and 'Archive'. The 'live or hold' sheet has a column with a data validation list, containing the list items: 'Open', 'Hold', 'Archive' ...
excel
Formulas and Functions
Macros and VBA
Chris_Munday's avatar
Chris_Munday
Copper Contributor
May 26, 2022

Hi Patrick2788,

 

Thanks very much for your response.

 

Is there a way to automatically delete the row from 'Open or Hold' when archive is selected from the list and it moves over to the 'archive' sheet?

 

So its almost like cutting and pasting the row over to the archive tab when archive is selected from the drop-down and deleting the blank row which is left.

 

In any case, I'm one step closer to the goal so thank you!

 

Chris

 

 

Patrick2788's avatar
Patrick2788
Silver Contributor
May 26, 2022
Automatically deleting the row will require code.
There are ways to conditionally format the rows to be removed so they appear 'blank' but that's a cell formatting trick - window dressing (The actual rows wouldn't be deleted).
  • Chris_Munday's avatar
    Chris_Munday
    Copper Contributor
    Jun 01, 2022

    FYI, I've found the code which works:

     

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim fromRow%, archiveRow%, archiveList As Worksheet
    If Target.Cells.Count > 1 Then Exit Sub

    If Not Application.Intersect(Target, Range("N2:N10")) Is Nothing Then 'amend this range address to your
    Set archiveList = ThisWorkbook.Worksheets("Archive")
    If Target.Value = "Archive" Then
    fromRow = ActiveCell.Row
    archiveRow = archiveList.Cells(archiveList.Rows.Count, 1).End(3).Row + 1
    Range(Cells(fromRow, 1), Cells(fromRow, 13)).Copy archiveList.Cells(archiveRow, 1)
    Rows(fromRow).EntireRow.Delete
    End If
    End If
    End Sub

     

     

    The sheet names will obviously need to be changed to correspond.

Resources