Move row in table to specific row in same table.

[ipbr21054]

I’ve had a search but only seen moving table rows to another table.

In this example it would work like this.
I’m looking to move say table row 20 to table Row 57

It would work by the user selecting a cell in column A in this case row 20.
Run the code & Insert a new row & put the new values in that row being row 57

Thanks.

 

[Joe4]

How about something like this?

Sub MyCopyInsertRow()

    Dim srw As Long, drw As Long
 
'   Get current selected row
    srw = ActiveCell.Row
   
'   Prompt for new row
    On Error GoTo err_chk
    drw = InputBox("What row what you like to copy to?")
    On Error GoTo 0
   
'   Insert new row and copy old row
    Rows(drw).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows(srw).Copy Range("A" & drw)
    Application.CutCopyMode = False
   
    Exit Sub
   
err_chk:
    If Err.Number = 13 Then
        MsgBox "You have not entered a valid row.", vbOKOnly, "ENTRY ERROR, PLEASE TRY AGAIN!"
    Else
        MsgBox Err.Number & ":" & Err.Description
    End If

End Sub

 

[ipbr21054]

Hi,
Thanks for that.
Can I ask what option is there for a static cell address.

I mean should the user wish to select a cell in column A but always to go to row 57

So as time goes in the user will select A10 & it’s sent to row 57

Later on a selection of A 99 & send to row 57

Thanks.
I can try both at the same time as opposed to coming back again.

 

[Joe4]

Simply hard-code the "drw" variable to 57, and then you can remove the error handling code, as it is no longer needed:

Sub MyCopyInsertRow()

    Dim srw As Long, drw As Long
   
'   Get current selected row
    srw = ActiveCell.Row
   
'   Hardcode destination row to row 57
    drw = 57
   
'   Insert new row and copy old row
    Rows(drw).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows(srw).Copy Range("A" & drw)
    Application.CutCopyMode = False
   
End Sub

Or even simpler:

Sub MyCopyInsertRow()

    Dim srw As Long
    
'   Get current selected row
    srw = ActiveCell.Row
    
'   Insert new row and copy old row
    Rows("57").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows(srw).Copy Range("A57")
    Application.CutCopyMode = False
    
End Sub

 

[ipbr21054]

Hi,
Looking at this code here but maybe i didnt explain correctly

The user will select a cell in column A,lets say A22
Running the code will copy values in Row 22
Insert a new row at A57
Paste the values from A22 row to A57 row
Move the rows up so row 22 isnt blank cells.

    Dim srw As Long
    
'   Get current selected row
    srw = ActiveCell.Row
    
'   Insert new row and copy old row
    Rows("57").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows(srw).Copy Range("A57")
    Application.CutCopyMode = False

 

[ipbr21054]

Does this help,i recorded a macro.

It copied the row A10 & inserted a new row at row 4 then pasted values & deleted row 10 value.

Only difference i would have the user select the cell a column A

    Range("A10:K10").Select
    Selection.Copy
    Rows("4:4").Select
    Selection.Insert Shift:=xlDown
    Application.CutCopyMode = False
    Range("A10:K10").Select
    Selection.ListObject.ListRows(10).Delete

 

[Joe4]

& deleted row 10 value

This is the part that I wasn't clear on. I could not tell if you wanted to simply copy the row and leave the original, or delete the original after it is copied.
Try this:

Sub MyCopyInsertRow()

    Dim srw As Long
    
'   Get current selected row
    srw = ActiveCell.Row
    
'   Add one if row to copy is after 57 to account for row insert
    If srw > 57 Then srw = srw + 1
    
'   Insert new row and copy old row
    Rows("57").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows(srw).Copy Range("A57")
    Application.CutCopyMode = False
    
'   Delete original row
    Rows(srw).Delete
    
End Sub

 

[ipbr21054]

@Joe4
Morning.
Looking at this code from last night i noticed that if the user had F23 slected & then run the code a new blank row is then inserted.
How can the code only be run by selecting a cell in column A

I have used the code before where it makes sure that a cell in column 1 is selected BUT i dont need to see a msgbox & confirm to it.

Just if in column 1 run the code.
If not in column 1 then just advise

 

[Alex Blakenburg]

After the Dim line add something like this:

    If ActiveCell.Column = 1 Then
        ' run your code
    Else
        ' put in your msgbox
    End If

I have seen some of your other posts so tell us it you are using a real table (listobject) here and the table name because column 1 for a Table is different to just column A.