Results 1 to 3 of 3

Transfer current row of data (Table) to a new row in a second table on the same sheet

Threaded View

  1. #1
    Forum Contributor
    Join Date
    04-15-2012
    Location
    California, USA
    MS-Off Ver
    Excel 2016 / Office 365
    Posts
    115

    Transfer current row of data (Table) to a new row in a second table on the same sheet

    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.
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] transfer data from one table to another table
    By pedjvak in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-31-2013, 04:24 AM
  2. UserForm to transfer items from one table to another table in the next empty cell...
    By aneese11 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-04-2012, 10:31 AM
  3. Transfer single column input data to fixed table in a separate Sheet
    By Prokis in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-07-2012, 05:37 AM
  4. [SOLVED] Current VBA Macro is slow. How can i speed up moving data from 1 sheet into a Pivot table?
    By kenadams378 in forum Excel Programming / VBA / Macros
    Replies: 39
    Last Post: 05-15-2012, 04:46 AM
  5. MACRO Transfer entire row based on a table in a third sheet/tab
    By eastydie in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-31-2011, 04:48 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1