Move and delete row when double clicking on cell

Kayfabe

New Member
Joined
Mar 24, 2023
Messages
11
Office Version
  1. 365
Platform
  1. MacOS
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:
VBA 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]
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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.

VBA Code:
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
 
Upvote 1
Solution
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.

VBA Code:
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top