Delete customers row of values after my code has completed moving its copy

[ipbr21054]

Morning,
On my worksheet i have customers name in column A & their values on that row.
Sometimes i need to move them up or down the page.
So for that i do the following.

Customer say is currently at row 10 & i need to move them to row 28
I select their name in column A, my userform opens which has basicalyy just copied the rows values & i am then asked what row to place these values in.
I enter 28 in the input field & the values are now placed in the row i entered being row 28

NOW i wish to delete the details from row 10 & this is where i need the advice please as currently im scrolling up / down looking for it.

Private Sub CommandButton1_Click()
 Dim i As Integer
 Dim ControlsArr As Variant, ctrl As Variant
 Dim x As Long
 Dim z As Integer
 
 z = CInt(Application.InputBox("WHICH ROW SHOULD DATA BE INSERTED INTO ?", "NEW CUSTOMER ROW NUMBER MESSAGE", Type:=1))
 
 ControlsArr = Array(Me.TextBox1, Me.TextBox2, Me.TextBox3, Me.TextBox4, Me.TextBox5, Me.TextBox6, Me.TextBox7, Me.TextBox8, Me.TextBox9, Me.TextBox10, Me.TextBox11)
    With ThisWorkbook.Worksheets("GRASS")
    .Rows(z).EntireRow.Insert Shift:=xlDown
    .Rows(z).RowHeight = 25
    .Rows(z).Font.Color = vbBlack
    .Rows(z).Font.Bold = True
    .Rows(z).Font.Size = 16
    .Rows(z).Font.Name = "Calibri"
    Range(.Cells(z, "A"), .Cells(z, "l")).Borders.LineStyle = xlContinuous
    Range(.Cells(z, "A"), .Cells(z, "l")).Borders.LineStyle = xlContinuous
    For i = 0 To UBound(ControlsArr)
    Select Case i
    Case -1
    .Cells(z, i + 1) = Val(ControlsArr(i))
    ControlsArr(i).Text = ""

    Case Else
    .Cells(z, i + 1) = ControlsArr(i)
    ControlsArr(i).Text = ""
    End Select
    Next i
    End With

 ActiveWorkbook.Save
    
 Unload MoveCustomerRow
 
 
End Sub

 

[mumps]

I would suggest doing away with the userform and trying this macro in the code module for your worksheet. All you have to do is double click any cell in column A and then enter the destination row number in the pop up.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column <> 1 Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim response As String
    response = InputBox("Enter the destination row number.")
    If response = "" Then
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        Exit Sub
    End If
    Rows(Target.Row).EntireRow.Copy
    Range("A" & response + 1).Insert
    Rows(Target.Row).Delete
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[ipbr21054]

Hi
The reason for the userform was so I could make a change.

Example.
Customer has Porsche in Textbox1 but when I run code and userform is shown I could delete Porsch & type Mini.
Then values are saved to worksheet.

So my records would show Mr Smith 001 Porsche.
Then next row Mr Smith 002 Mini

I then get to keep a record of what job I did for him even when he changed his car.

 

[mumps]

There is more information that would be necessary to find a solution. It would be easier to help if you could upload a copy of your file including any macros you are currently using, to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[ipbr21054]

Here is the uploaded file for you to look at.
I didnt say it was a table,message to say attempting to move etc etc.

Basically allow the move & current value just move down a row & allow the paste to continue.

DOWNLOAD FILE HERE

 

[ipbr21054]

Update.
I was confusing two worksheets.

This doesn’t need to be altered in respect of Porsche to mini etc.

The post was to allow values to be moved to another row BUT delete the initial row of values.

Also as mentioned all within a table.
Thanks.

 

[mumps]

Which button will you click to move the values to another row?

 

[ipbr21054]

I have a button in the sheet, last one on the right but I believe I removed code to use your double click.

Do you want me to put it back & upload file again ?

 

[ipbr21054]

Below is the code from worksheet button then see also next code

Private Sub MoveCustomer_Click()

If ActiveCell.Column = 1 Then

MoveCustomerRow.Show

Else

MsgBox "YOU DIDNT SELECT A CUSTOMER IN COLUMN A", vbCritical, "MOVE CUSTOMER ROW MESSAGE"

End If

End Sub

Private Sub CommandButton1_Click()
 Dim i As Integer
 Dim ControlsArr As Variant, ctrl As Variant
 Dim x As Long
 Dim z As Integer
 
 z = CInt(Application.InputBox("WHICH ROW SHOULD DATA BE INSERTED INTO ?", "NEW CUSTOMER ROW NUMBER MESSAGE", Type:=1))
 
 ControlsArr = Array(Me.TextBox1, Me.TextBox2, Me.TextBox3, Me.TextBox4, Me.TextBox5, Me.TextBox6, Me.TextBox7, Me.TextBox8, Me.TextBox9, Me.TextBox10, Me.TextBox11)
    With ThisWorkbook.Worksheets("GRASS")
    .Rows(z).EntireRow.Insert Shift:=xlDown
    .Rows(z).RowHeight = 25
    .Rows(z).Font.Color = vbBlack
    .Rows(z).Font.Bold = True
    .Rows(z).Font.Size = 16
    .Rows(z).Font.Name = "Calibri"
    Range(.Cells(z, "A"), .Cells(z, "l")).Borders.LineStyle = xlContinuous
    Range(.Cells(z, "A"), .Cells(z, "l")).Borders.LineStyle = xlContinuous
    For i = 0 To UBound(ControlsArr)
    Select Case i
    Case -1
    .Cells(z, i + 1) = Val(ControlsArr(i))
    ControlsArr(i).Text = ""

    Case Else
    .Cells(z, i + 1) = ControlsArr(i)
    ControlsArr(i).Text = ""
    End Select
    Next i
    End With

 ActiveWorkbook.Save
    
 Unload MoveCustomerRow
 
 
End Sub

Even if you just edited my code to delete inital row after all done

 

[mumps]

Replace the current macro with this one:

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    Dim ControlsArr As Variant, ctrl As Variant, lRow As Long, x As Long, z As Long, i As Long
    lRow = ActiveCell.Row
    z = CInt(Application.InputBox("WHICH ROW SHOULD DATA BE INSERTED INTO ?", "NEW CUSTOMER ROW NUMBER MESSAGE", Type:=1))
    ControlsArr = Array(Me.TextBox1, Me.TextBox2, Me.TextBox3, Me.TextBox4, Me.TextBox5, Me.TextBox6, Me.TextBox7, Me.TextBox8, Me.TextBox9, Me.TextBox10, Me.TextBox11)
    With ThisWorkbook.Worksheets("GRASS")
        .Rows(z + 1).EntireRow.Insert Shift:=xlDown
        .Rows(z + 1).RowHeight = 25
        .Rows(z + 1).Font.Color = vbBlack
        .Rows(z + 1).Font.Bold = True
        .Rows(z + 1).Font.Size = 16
        .Rows(z + 1).Font.Name = "Calibri"
        Range(.Cells(z + 1, "A"), .Cells(z, "l")).Borders.LineStyle = xlContinuous
        Range(.Cells(z + 1, "A"), .Cells(z, "l")).Borders.LineStyle = xlContinuous
        For i = 0 To UBound(ControlsArr)
            Select Case i
                Case -1
                    .Cells(z + 1, i + 1) = Val(ControlsArr(i))
                    ControlsArr(i).Text = ""
                Case Else
                    .Cells(z + 1, i + 1) = ControlsArr(i)
                    ControlsArr(i).Text = ""
            End Select
        Next i
    End With
    Sheets("GRASS").Rows(lRow).Delete
    ActiveWorkbook.Save
    Unload MoveCustomerRow
    Application.ScreenUpdating = True
End Sub