Ok I am having trouble getting transferring a row of data from one Table to another using a button. I use the code (and various versions of it) listed below successfully from worksheet to worksheet and even from one workbook to a separate closed workbook. But cannot seem to figure out how to do it from Table2 to Table3 when both tables are on the same sheet.
I have attached a workbook that has 6 tables on one sheet. I need to transfer the current selected row from table 1-2, 3-4, and 5-6. Then delete the current selected row from the primary table.
Sub LineShipped_Click()
'Code in this module will copy the selected row to the second worksheet
Dim cl As Range
Dim r As Long, lr As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Application.ScreenUpdating = False
Set cl = ActiveCell
r = cl.Row
Set ws2 = Sheets("Sheet2")
lr = ws2.Range("A" & Rows.Count).End(xlUp).Row
Range("A" & r, Range("Z" & r)).Copy Destination:=ws2.Range("A" & lr + 1)
ActiveCell.EntireRow.Delete 'Delete the row with with the active cell
Rows("59:59").Select 'Select the last row in the named range
Selection.Insert Shift:=xlUp, CopyOrigin:=xlFormatFromLeftOrAbove 'Insert a new row to keep the named range with 60 rows
Cells(59, 1).Value = Cells(58, 1).Value
Cells(3, 1).Activate 'Acitvate a new cell at the top of the sheet
Application.ScreenUpdating = True
End Sub
I am not having any luck modifying the above code to work with tables on the same sheet. Any help with modification of the code, to
Copy the values of the current selected row
Insert a new row at the end of the second table / Add data to the next available row (NOTE I would like to add the row if the table does not have any rows left to use).
Delete the current selected row from the first table.
Bookmarks