Auto move row to a different sheet in the same file after selecting from a drop down list

Question

Hi! I'm trying to set up this Excel sheet so that when I select "Archived" in the drop down menu in column D, it moves that entire row to the "Archive" sheet. Can someone help me out? I have very minimal experience with Excel sheets so something step by step would be helpful. Based on looking stuff up online it looks like I'll need to use the VBA Editor but I don't know much about writing code so I'd really appreciate some help!

Answer 1

Hello Genevieve Ortiz,

Welcome to Microsoft Q&A forum.

I understand why you're feeling unsure about set up an Excel automation, it can be confusing when Excel tasks require more than just built-in features.

The main cause is that Excel doesn't have a native function to automatically move rows based on dropdown selections, so it requires VBA scripting to achieve this behavior.

Based on what described, here are steps that you can try:

Step 1. Prepare Your Workbook

• A main sheet (e.g., Tasks) with your data and the dropdown in column D.
• A target sheet named Archive.

Step 2. Open the VBA Editor

• Press Alt + F11 to open the VBA editor.
• In the left pane, double-click ThisWorkbook.

Step 3. Insert the VBA Code: Copy and paste this code into the ThisWorkbook module:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim wsSource As Worksheet, wsArchive As Worksheet

    Dim rng As Range

    ' Set your sheets

    Set wsSource = ThisWorkbook.Sheets("Tasks")

    Set wsArchive = ThisWorkbook.Sheets("Archive")

    ' Check if change is in column D and only one cell changed

    If Not Intersect(Target, wsSource.Range("D:D")) Is Nothing And Target.Count = 1 Then

        If LCase(Target.Value) = "archived" Then

            Application.EnableEvents = False

            ' Find next empty row in Archive sheet

            Dim nextRow As Long

            nextRow = wsArchive.Cells(wsArchive.Rows.Count, "A").End(xlUp).Row + 1

            ' Copy the entire row

            Target.EntireRow.Copy wsArchive.Rows(nextRow)

            ' Delete the row from source

            Target.EntireRow.Delete

            Application.EnableEvents = True

        End If

    End If

End Sub

Step 4. Enable Macros

• Save the file as .xlsm (macro-enabled).
• Close and reopen the workbook, then enable macros.

Step 5. Test It

• In the Tasks sheet, select "Archived" from the dropdown in column D.
• The row should move to the Archive sheet automatically.

------------------------------------------------------------------------------------------------------------ 

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".  

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.