VBA to move rows in one sheet based on data validation

MaryBob · Jun 14th 2022

Hi,

I am looking for a VBA that moves rows based on data validation selection in same Sheet

On the same Sheet there is a paint section at the top and polish at the bottom, I need a vba to move the row to paint section if Paint is selected in data validation and then back down to polish if polish is selected.

I have attached a spreadsheet to give an idea of what I am looking for - Data Validation in Col L

2022 PAINTSHOP MANAGEMENT.xlsx

Mumps · Jun 23rd 2022

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. Make a selection in column L.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 12 Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim rRow As Long
    rRow = Range("B2", Range("A" & Rows.Count).End(xlUp)).Find("Date In", LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Select Case Target.Value
        Case "Back to Paint"
            Target.EntireRow.Copy
            Rows(2).Insert
            Target.EntireRow.Delete
        Case "In Polish"
            Target.EntireRow.Copy
            Rows(rRow + 1).Insert
            Target.EntireRow.Delete
    End Select
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Display More

VBA to move rows in one sheet based on data validation

Participate now!

Share

Similar Threads

VBA copy / paste values based on header title

VBA copy data from multiple workbooks based on criteria

Move certain data from one column to another sheet based on selection in drop down selection

VBA Code Modification, Command button to copy data based on row number.

Group Workbooks in a Folder based on Number of Rows

How can this code be modified to show the result on more sheets based on a condition ?

Tags