Move and delete row when double clicking on cell

[Kayfabe]

Expected behavior:
When naming the first cell in a row and double clicking it, the row is copied, deletes and removes itself from the sheet and is then placed on another sheet with the same name as entered. Rows on 2nd sheet should not overwrite.

Ask:
I found the code below written by My Answer Is which does everything but remove the row where the rest of the rows shift up to fill the gap. I've enclosed the script below. Any help is really appreciated. Thanks in advance.

Code:

Expected behavior: When doing something like double click a cell, move a row to another sheet

In searching, I found the code below from And The

In reviewing[ICODE]
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  7/15/2022  12:41:36 AM  EDT
If Target.Column = 1 Then
On Error GoTo M
Dim r As Long
Dim ans As String
ans = Target.Value
r = Target.Row
Dim Lastrow As Long
Lastrow = Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row + 1
Rows(r).Copy Sheets(ans).Rows(Lastrow)
Rows(r).ClearContents
End If
Exit Sub
M:
MsgBox "You double clicked on " & ans & vbNewLine & "This sheet does not exist"
End Sub
[/ICODE]

 

[kevin9999]

If I understand you correctly, you want to copy the entire row from the sheet (with the double-click code attached) to the sheet that is named in column A? And then delete that row? If that's the case, then try the following on a copy of your sheet.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    On Error GoTo Escape
    Application.EnableEvents = False
    If Target.Column = 1 Then
        Cancel = True
        Dim ws As String
        ws = Target.Value2
        With Target.EntireRow
            .Copy Worksheets(ws).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
            .Delete
        End With
    End If
Continue:
        Application.EnableEvents = True
        Exit Sub
Escape:
        MsgBox "Error " & Err.Number & ": " & Err.Description
        Resume Continue
End Sub

 

[Kayfabe]

If I understand you correctly, you want to copy the entire row from the sheet (with the double-click code attached) to the sheet that is named in column A? And then delete that row? If that's the case, then try the following on a copy of your sheet.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    On Error GoTo Escape
    Application.EnableEvents = False
    If Target.Column = 1 Then
        Cancel = True
        Dim ws As String
        ws = Target.Value2
        With Target.EntireRow
            .Copy Worksheets(ws).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
            .Delete
        End With
    End If
Continue:
        Application.EnableEvents = True
        Exit Sub
Escape:
        MsgBox "Error " & Err.Number & ": " & Err.Description
        Resume Continue
End Sub

Thank you so very much!! This worked perfectly.

 

[kevin9999]

Thank you so very much!! This worked perfectly.

Happy to help, and thanks for the feedback

 

[smozgur]

Thank you so very much!! This worked perfectly.

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.