Select, cut, delete row, paste VBA

[chiefskingdom98]

Hello,

I am working on a data entry table and have run across some questions. I was wondering if there was a way to create a button or box per row in the table to select that row. From there I want to cut that row out of the original table, delete the row, and then paste the row to a separate worksheet in the next blank row. I want it to keep the values of the cells the same and not get funky with the formulas that are in there as well. I understand that you can click on the left side of a row in a table to select it but I am hoping to make something a little bit more visual and easy to find for the people who will be using this worksheet. Once they have selected said row, I want to run a button command that will cut it, delete the row from the old table, and then paste into the next blank row in the new spreadsheet. Currently lost on that part, please help! Thanks

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Double click on any cell in column A and that row will be copied to a sheet named Bravo.
This assumes you have a value in column A
Modify the script to the sheet name you want
And the row will be deleted from the original sheet

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  10/20/2020  11:12:03 PM  EDT
Cancel = True
If Target.Column = 1 Then
Dim Lastrow As Long
Dim sn As String
sn = "Bravo" 'Change copy to sheet name if needed
Lastrow = Sheets(sn).Cells(Rows.Count, "A").End(xlUp).Row + 1
Rows(Target.Row).Copy Sheets(sn).Cells(Lastrow, 1)
Rows(Target.Row).Delete
End If
End Sub

 

[chiefskingdom98]

Thank you!

Here is the code I found yesterday and have been playing around with.

Private Sub CommandButton1_Click()
    Dim x As Integer
    Dim y As Integer
    Dim i As Integer
    Dim shSource As Worksheet
    Dim shTarget1 As Worksheet

    Set shSource = ThisWorkbook.Sheets("Production Tracker")
    Set shTarget1 = ThisWorkbook.Sheets("Record")

    If shTarget1.Cells(2, 7).Value = "" Then
        x = 2
    Else
        x = shTarget1.Cells(2, 7).CurrentRegion.Rows.Count + 1
    End If

    i = 2

    Do Until shSource.Cells(i, 7) = ""
        If shSource.Cells(i, 7).Value = "Ready" Then
            shSource.Rows(i).Copy
            shTarget1.Cells(x, 1).PasteSpecial Paste:=xlPasteValues
            shSource.Rows(i).Delete
            x = x + 1
            GoTo Line1
        End If
        i = i + 1
Line1:     Loop

End Sub

Problem is I am losing my formatting now on certain columns such as date. Is there a way to copy the value itself instead of the function that exists in the cell?

 

[My Aswer Is This]

Thank you!

Here is the code I found yesterday and have been playing around with.

Private Sub CommandButton1_Click()
    Dim x As Integer
    Dim y As Integer
    Dim i As Integer
    Dim shSource As Worksheet
    Dim shTarget1 As Worksheet

    Set shSource = ThisWorkbook.Sheets("Production Tracker")
    Set shTarget1 = ThisWorkbook.Sheets("Record")

    If shTarget1.Cells(2, 7).Value = "" Then
        x = 2
    Else
        x = shTarget1.Cells(2, 7).CurrentRegion.Rows.Count + 1
    End If

    i = 2

    Do Until shSource.Cells(i, 7) = ""
        If shSource.Cells(i, 7).Value = "Ready" Then
            shSource.Rows(i).Copy
            shTarget1.Cells(x, 1).PasteSpecial Paste:=xlPasteValues
            shSource.Rows(i).Delete
            x = x + 1
            GoTo Line1
        End If
        i = i + 1
Line1:     Loop

End Sub

Problem is I am losing my formatting now on certain columns such as date. Is there a way to copy the value itself instead of the function that exists in the cell?

So are you saying the code I provided does not do what you want.
What does it not do the way you want?