Create VBA Cut/Paste Row from one Sheet to Another

[oscardwilde]

I work for a covered RV storage facility and am creating a waitlist for popular spaces. I'm trying to create a way for a row of data to be moved from the main sheet to another sheet when a drop down cell is selected to "Yes" as the value. That value resides in column R on the waitlist sheet and I want the row to be moved to the "Removed" sheet so that we can keep track of when someone is removed from the list. I can't seem to figure out how to get it there and keep adding to the removed sheet without losing the other data. Any ideas how I can make this happen?

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your waitlist 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. Select a value in column R.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("R:R")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Target = "Yes" Then
        With Sheets("Removed")
            Range("A" & Target.Row & ":Q" & Target.Row).Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
            Target.EntireRow.Delete
        End With
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[oscardwilde]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your waitlist 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. Select a value in column R.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("R:R")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Target = "Yes" Then
        With Sheets("Removed")
            Range("A" & Target.Row & ":Q" & Target.Row).Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
            Target.EntireRow.Delete
        End With
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Thank you. This worked better than what I had but the data doesn't end up on the "Removed" tab. Not sure where it goes. Also, is it possible to Cut the data and Paste it onto the removed sheet rather than deleting it and losing the row on the original sheet?

 

[oscardwilde]

Thank you. This worked better than what I had but the data doesn't end up on the "Removed" tab. Not sure where it goes. Also, is it possible to Cut the data and Paste it onto the removed sheet rather than deleting it and losing the row on the original sheet?

Actually I found it on the removed sheet, it was just below all the other fields that I forgot I had setup when I copied the tab. Still would like to know if I could cut and paste rather than delete the row from the original sheet though.

 

[mumps]

When you say “cut” that means deleting it from its original location. Do you mean “copy” and paste?

 

[oscardwilde]

You know when you cut a cell and paste it somewhere else, it actually moves that data without deleting the row. Or is that not possible because it's a row of data I am moving?

 

[mumps]

"Cut" is the same as "Delete". See if this does what you want:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("R:R")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Target = "Yes" Then
        With Sheets("Removed")
            Range("A" & Target.Row & ":Q" & Target.Row).Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
        End With
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[oscardwilde]

That didn't work as well. I think I can work with the first one though and just adjust my filter and sort formulas on the other sheets. Thank you for all your help! You are amazing.

 

[mumps]

You are very welcome.