Thread: Move entire row to another worksheet based on cell value

I would like them to be moved to the other worksheet and then be deleted from the current pipeline. When they are added to the booked or dnmq workbook, can we have them be automatically placed on the next open row?

Basically if I select Booked then they will move to booked worksheet on the next free row.
If i select DNMQ then they will move to DNMQ worksheet on the next free row.

Hope this helps and thanks for your help!

Thanks for your help. Is there a way to have once the status gets changed to booked or dnmq for it to delete from the current pipeline sheet? It moves over perfectly!

Originally Posted by stanleydgromjr

bdf0827,

Thanks for the workbook.


Detach/open workbook Worksheet_Change Event wC colD wB wD bdf0827 - EF790860 - SDG12.xlsm and make changes to the DataValidation cells in worksheet Current in range D7:D26.

Then check the other two worksheets.



If you want to use the macro on another workbook:


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run, worksheet Current
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' stanleydgromjr, 09/03/2011
' http://www.excelforum.com/excel-programming/790860-move-entire-row-to-another-worksheet-based-on-cell-value.html
If Intersect(Target, Range("D7:D26")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
Dim NR As Long
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  Select Case Target.Value
    Case "Booked"
      NR = Worksheets("Booked").Range("D29").End(xlUp).Offset(1).Row
      Range("A" & Target.Row & ":J" & Target.Row).Copy Worksheets("Booked").Range("A" & NR)
    Case "DNMQ"
      NR = Worksheets("DNMQ").Range("D29").End(xlUp).Offset(1).Row
      Range("A" & Target.Row & ":J" & Target.Row).Copy Worksheets("DNMQ").Range("A" & NR)
  End Select
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then make changes to the DataValidation cells in worksheet Current in range D7:D26.

Then check the other two worksheets.

Oh and I forgot also, do you know if once it deletes from the current pipeline worksheet if the rows can be moved up so there is no blank row?

Originally Posted by stanleydgromjr

bdf0827,

Thanks for the workbook.


Detach/open workbook Worksheet_Change Event wC colD wB wD bdf0827 - EF790860 - SDG12.xlsm and make changes to the DataValidation cells in worksheet Current in range D7:D26.

Then check the other two worksheets.



If you want to use the macro on another workbook:


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run, worksheet Current
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' stanleydgromjr, 09/03/2011
' http://www.excelforum.com/excel-programming/790860-move-entire-row-to-another-worksheet-based-on-cell-value.html
If Intersect(Target, Range("D7:D26")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
Dim NR As Long
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  Select Case Target.Value
    Case "Booked"
      NR = Worksheets("Booked").Range("D29").End(xlUp).Offset(1).Row
      Range("A" & Target.Row & ":J" & Target.Row).Copy Worksheets("Booked").Range("A" & NR)
    Case "DNMQ"
      NR = Worksheets("DNMQ").Range("D29").End(xlUp).Offset(1).Row
      Range("A" & Target.Row & ":J" & Target.Row).Copy Worksheets("DNMQ").Range("A" & NR)
  End Select
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then make changes to the DataValidation cells in worksheet Current in range D7:D26.

Then check the other two worksheets.

Works perfectly, thank you a million times for your help!!!!

Originally Posted by stanleydgromjr

bdf0827,

Thanks for the workbook.


Detach/open workbook Worksheet_Change Event wC colD wB wD bdf0827 - EF790860 - SDG12.xlsm and make changes to the DataValidation cells in worksheet Current in range D7:D26.

Then check the other two worksheets.



If you want to use the macro on another workbook:


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run, worksheet Current
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' stanleydgromjr, 09/03/2011
' http://www.excelforum.com/excel-programming/790860-move-entire-row-to-another-worksheet-based-on-cell-value.html
If Intersect(Target, Range("D7:D26")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
Dim NR As Long
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  Select Case Target.Value
    Case "Booked"
      NR = Worksheets("Booked").Range("D29").End(xlUp).Offset(1).Row
      Range("A" & Target.Row & ":J" & Target.Row).Copy Worksheets("Booked").Range("A" & NR)
    Case "DNMQ"
      NR = Worksheets("DNMQ").Range("D29").End(xlUp).Offset(1).Row
      Range("A" & Target.Row & ":J" & Target.Row).Copy Worksheets("DNMQ").Range("A" & NR)
  End Select
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then make changes to the DataValidation cells in worksheet Current in range D7:D26.

Then check the other two worksheets.

Originally Posted by gjlindn

Rolls data up to fill empty row

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 4 And Target.Cells.Count = 1 Then MoveMe Target
End Sub

Sub MoveMe(rTarget As Range)
    Dim wsMoveTo    As Worksheet
    Dim rMoveToRow  As Range
    Dim rCopyFrom   As Range
    Dim rCopyTo     As Range
    Dim rItem       As Range
    Dim rMoveUp     As Range
    Dim bEvents     As Boolean
    Dim bScrUpd     As Boolean
    Dim lCalc       As Long
    
    With Application
        bEvents = .EnableEvents
        bScrUpd = False
        lCalc = .Calculation
        .EnableEvents = False
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    

    Select Case rTarget.Text
        Case "Booked", "DNMQ"
            Set wsMoveTo = Sheets(rTarget.Text)
        Case Else
            Exit Sub
    End Select
    Set rMoveToRow = wsMoveTo.Cells.Find("Potential", LookIn:=xlValues, LookAt:=xlWhole).End(xlUp).Offset(1)
    Set rCopyFrom = Range(Cells(rTarget.Row, "A"), Cells(rTarget.Row, "J"))
    Set rCopyTo = wsMoveTo.Range(wsMoveTo.Cells(rMoveToRow.Row, "A"), wsMoveTo.Cells(rMoveToRow.Row, "J"))
    rCopyTo.Formula = rCopyFrom.Formula
    Set rMoveUp = Range(Cells.Find("Potential", LookIn:=xlValues, _
        LookAt:=xlWhole).End(xlUp).Offset(1), rCopyFrom)
    rMoveUp.Formula = rMoveUp.Offset(1).Formula

    
    Set wsMoveTo = Nothing
    Set rMoveToRow = Nothing
    Set rCopyFrom = Nothing
    Set rCopyTo = Nothing
    
    With Application
        .EnableEvents = bEvents
        .ScreenUpdating = bScrUpd
        .Calculation = lCalc
    End With
End Sub

I'm trying to figure out how to mark this as solved. I've tried clicking all around.

The minor changes somehow messed up the coding and I don't know which item did it.

Originally Posted by gjlindn

Formula updates.

The DNMQ works fine, but the booked moves them below the totals for some reason. Any ideas?

Originally Posted by stanleydgromjr

bdf0827,

Per your latest request "after copying the Target.Row, delete the Target.Row".


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run, worksheet Current
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' stanleydgromjr, 09/04/2011
' Version 2, after copying the Target.Row, delete the Target.Row
' http://www.excelforum.com/excel-programming/790860-move-entire-row-to-another-worksheet-based-on-cell-value.html
If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
Dim NR As Long
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  Select Case Target.Value
    Case "Booked"
      NR = Worksheets("Booked").Range("D29").End(xlUp).Offset(1).Row
      Range("A" & Target.Row & ":J" & Target.Row).Copy Worksheets("Booked").Range("A" & NR)
      Rows(Target.Row).Delete
    Case "DNMQ"
      NR = Worksheets("DNMQ").Range("D29").End(xlUp).Offset(1).Row
      Range("A" & Target.Row & ":J" & Target.Row).Copy Worksheets("DNMQ").Range("A" & NR)
      Rows(Target.Row).Delete
  End Select
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub

If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.