Select, cut, delete row, paste VBA

chiefskingdom98

New Member
Joined
Oct 20, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,409
Office Version
  1. 2013
Platform
  1. Windows
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
VBA Code:
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

New Member
Joined
Oct 20, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Thank you!

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

VBA Code:
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,409
Office Version
  1. 2013
Platform
  1. Windows
Thank you!

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

VBA Code:
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,348
Messages
5,624,145
Members
416,014
Latest member
MickP69

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
Top