Move a row based on drop down list to another sheet

Question

Hi I have a spreadsheet that i would like to be able to automatically move a row to another sheet based on the selection from a drop down menu. As you can see in the screenshot below, the column with the drop down is Status (col I). There is a drop down list that entries correspond to the names of the worksheets. eg If I select "Zone1" in the column the whole row (Cols A-M) will automatically move to the worksheet "Zone1". The original entry does not stay in the "Register" sheet. Then if I want to move the row again I would go to where the record is located and the same thing happens when I change the status.

Please I am not a VBA expert so I need small steps!

Thanks in anticipation!

Answer 1

Here are the steps to create a VBA code that will move a row to another sheet based on the selection from a drop-down menu:

1. Open the Excel workbook and press Alt + F11 to open the Visual Basic Editor.
2. In the Project Explorer window, double-click on the sheet where you want to add the code (in this case, the "Register" sheet).
3. In the code window, select Worksheet from the left drop-down menu and Change from the right drop-down menu.
4. Copy and paste the following code into the code window:

======================================

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet

Dim rng As Range

Dim lastRow As Long

'Check if the changed cell is in column I (Status)

If Target.Column = 9 Then

    'Get the selected worksheet name

    Set ws = Worksheets(Target.Value)

    'Get the last row in the selected worksheet

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1

    'Copy the row to the selected worksheet

    Set rng = Range("A" & Target.Row & ":M" & Target.Row)

    rng.Copy ws.Range("A" & lastRow)

    'Delete the row from the Register sheet

    rng.Delete Shift:=xlUp

End If

End Sub

===========================

5. Save the workbook and close the Visual Basic Editor.

Now, whenever you change the value in column I (Status), the code will check if the selected worksheet exists and copy the row to that worksheet. It will also delete the row from the "Register" sheet. Note that the code assumes that the worksheet names in the drop-down list match the actual worksheet names in the workbook. If there is no matching worksheet, the code will generate an error.

Answer 2

Thank you so much for your help! It worked exactly how I hoped. I am about to post another question....you may be able to help me there too!

Answer 3

You are wlecome. I will try.

Answer 4

I tried using this code for a different data range in column G on my own workbook and nothing happens.

Can you help?

Answer 5

Share a test workbook.