+ Reply to Thread
Results 1 to 6 of 6

Move data from 1 table to another table, then clear the original table

  1. #1
    Registered User
    Join Date
    06-20-2013
    Location
    US
    MS-Off Ver
    Office 365
    Posts
    53

    Move data from 1 table to another table, then clear the original table

    I'm having issues getting table 1 to clear.
    See attached.
    Table1 is on the tab "Input"
    Table2 is on the tab "MasterTable"

    When table 1 is blank, it should only have 1 entry row. If the user wants to input 3 rows, tables automatically grow if the user inputs a 2nd record, or a 3rd, and so on.
    After the user inputs as much as they want, they will click the button to move the newly input data into the MasterTable (table2)

    But then I need to come back to Table1 and clear what they had input, and I also want Table1 to have only 1 new row ready for input for the next user.

    I think I'm close, but it just doesn't work. I currently have 3 lines of code to clear Table1 remarked out.

    How do I fix this?
    Attached Files Attached Files

  2. #2
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Move data from 1 table to another table, then clear the original table

    Try this one.
    Please Login or Register  to view this content.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  3. #3
    Registered User
    Join Date
    06-20-2013
    Location
    US
    MS-Off Ver
    Office 365
    Posts
    53

    Re: Move data from 1 table to another table, then clear the original table

    Thank you bakerman2!
    One item I need to add is (I think) "ActiveSheet.UsedRange" at some point before inserting into Table2. Because table 2 could potentially have rows deleted, the last active cell isn't known until the file is saved, but saving isn't always going to happen. So my understanding is this 1 line "AciveSheet.UsedRange" updates what the last active cell is. But in your code I don't see Table2 even mentioned so I'm not sure where I would insert this line of code. But I'm also getting an error now (I haven't tried inserting this UsedRange yet either. I got the error after testing a few rounds of inserting new records and also deleting some rows in Table2. The record moves/inserts, then it errors and Table1 doesn't clear itself. I'll see if I can attach the latest version of this file.

  4. #4
    Registered User
    Join Date
    06-20-2013
    Location
    US
    MS-Off Ver
    Office 365
    Posts
    53

    Re: Move data from 1 table to another table, then clear the original table

    Attaching latest copy of test file...
    Attached Files Attached Files

  5. #5
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Move data from 1 table to another table, then clear the original table

    This will get you started.
    Please Login or Register  to view this content.
    Last edited by bakerman2; 04-03-2019 at 06:45 AM.

  6. #6
    Registered User
    Join Date
    06-20-2013
    Location
    US
    MS-Off Ver
    Office 365
    Posts
    53

    Re: Move data from 1 table to another table, then clear the original table

    Only the last line gave me issues. I don't know why. But I changed it to the following and now it works. Thank you again bakerman2

    Sub MoveInputToMasterTable()
    Dim lRow As Long

    With Sheets("Input").ListObjects("Table1").DataBodyRange
    lRow = Sheet4.ListObjects("Table2").Range.Rows.Count
    .Copy Sheet4.Range("A" & lRow).Offset(1)
    If .Rows.Count > 1 Then
    .Offset(1).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
    .Rows(1).SpecialCells(xlCellTypeConstants).ClearContents
    Else
    .Rows(1).SpecialCells(xlCellTypeConstants).ClearContents
    End If
    End With
    Application.Goto Reference:="R5C1"
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Duplicate a Table and Automatically have it Update as Original Table Updates?
    By tomprestriidge in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-16-2018, 12:40 PM
  2. Replies: 1
    Last Post: 05-14-2016, 03:05 PM
  3. [SOLVED] Code to move data from an entry table to a historical table by date.
    By rlh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-17-2014, 11:04 AM
  4. Replies: 1
    Last Post: 11-28-2013, 02:10 AM
  5. Replies: 2
    Last Post: 11-22-2013, 02:52 PM
  6. Help creating vba function to move data from table to table on if condtion
    By damaple in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-09-2013, 05:42 AM
  7. Creating new table from selective data in original table
    By rasmussk in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-18-2009, 09:46 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