+ Reply to Thread
Results 1 to 10 of 10

Copying a Table Row to Another Table and Then Deleting the Original

  1. #1
    Registered User
    Join Date
    02-02-2022
    Location
    Los Angeles, United States
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    6

    Unhappy Copying a Table Row to Another Table and Then Deleting the Original

    Hi! I am new at VBA and as an excercise I am attempting this challenge. I tried to adapt a pre-existing code to my needs and I believe I have understood each function in my code pretty well, but I did realize the code was originally made 9 years ago, so it may be outdated...

    Please Login or Register  to view this content.

    The code is meant to check if the Status collumn (which is collumn F) of my CurrentTable has the word "PAST" in it. If it does have that word, I want it to copy that row and paste it inside the table PastTable located at a sheet named "PAST". After it pastes the row, I want the original to be deleted.


    CONTEXT (in case someone wants to suggest a different method):

    In my original file I have a table with lots of Conditional Formatting rules. Whenever we try to copy and paste the row onto a different sheet, the Conditional Formatting rules force their way in there, so the format ends up being a mess. Since we are talking about Tables, you can't Special Paste the data. This is the solution I thought was best to solve this issue.
    Attached Files Attached Files
    Last edited by BadWithVBA; 02-02-2022 at 06:44 PM. Reason: Adding Example File

  2. #2
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Copying a Table Row to Another Table and Then Deleting the Original

    Welcome to the Forum BadWithVBA...
    No need for a loop...
    Please Login or Register  to view this content.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  3. #3
    Registered User
    Join Date
    02-02-2022
    Location
    Los Angeles, United States
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    6

    Re: Copying a Table Row to Another Table and Then Deleting the Original

    You just made everything so simple... Thank you, kindly!

  4. #4
    Registered User
    Join Date
    02-02-2022
    Location
    Los Angeles, United States
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    6

    Re: Copying a Table Row to Another Table and Then Deleting the Original

    I just encountered a new problem... If there is no cell with "PAST" in the collumn, it will send EVERY row of the table to the other one. I think in the solution could be adding a condition where it only does the action that you have sent to me if there is any data at all that reads as "PAST", but I'm having a really hard time figuring out how to turn this into code.
    Last edited by BadWithVBA; 02-03-2022 at 06:23 PM.

  5. #5
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Copying a Table Row to Another Table and Then Deleting the Original

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    02-02-2022
    Location
    Los Angeles, United States
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    6

    Re: Copying a Table Row to Another Table and Then Deleting the Original

    Ok turns out that also sends everything if there's no "PAST" cell. I tried counting the Selection.Rows.Count and comparing that to my table's .Range.Rows.Count but if there's 0 in the filter, Selection.Rows.Count returns a 1. It would ignore it if it only had 1 row to send.

    EDIT: that actually doesn't even recognize the actual selection... A shame. It shows 1 no matter what.
    Last edited by BadWithVBA; 02-04-2022 at 04:29 PM.

  7. #7
    Registered User
    Join Date
    02-02-2022
    Location
    Los Angeles, United States
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    6

    Re: Copying a Table Row to Another Table and Then Deleting the Original

    Ok... I've done my actual method now with a mix of yours and some of my brain. This is it:

    Please Login or Register  to view this content.
    My problem now is (lol sorry) it's only adding 1 row to the other table when I need it to add as many as it needs, so only the first copied row becomes part of the PastTable but the rest of the copied rows are outside of the table.

    EDIT: I don't understand why it used to actually add them all to the table before and now it isn't... When it would send the whole table it'd be fine but now when it sends individual rows it isn't.
    Last edited by BadWithVBA; 02-04-2022 at 07:10 PM.

  8. #8
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Copying a Table Row to Another Table and Then Deleting the Original

    The code provided in Post 5 does as required with the sample file supplied...Why are you now going back to looping?...Unless your actual file is not setup the same way?
    I suggest uploading a file representing the actual problem you are encountering...
    Last edited by sintek; 02-05-2022 at 02:43 AM.

  9. #9
    Registered User
    Join Date
    02-02-2022
    Location
    Los Angeles, United States
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    6

    Re: Copying a Table Row to Another Table and Then Deleting the Original

    Alright, here is an updated database. Sorry for the late response, by the way. The file already has the macro setup and you might see I've added a column to specify the folder to make it simpler (or so I thought).

    The compiler now even shows an error with a message about not being able to use the copy command on multiple selections. It seems to be adding to the table properly now (don't know what changed), but that's the new issue I encountered...
    Attached Files Attached Files

  10. #10
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Copying a Table Row to Another Table and Then Deleting the Original

    Please Login or Register  to view this content.

+ 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. Replies: 1
    Last Post: 07-28-2020, 10:30 AM
  2. Move data from 1 table to another table, then clear the original table
    By kwoltman in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-03-2019, 06:06 PM
  3. Replies: 11
    Last Post: 04-29-2017, 07:23 PM
  4. Deleting/Copying from Pivot Table
    By LiamR95 in forum Excel General
    Replies: 1
    Last Post: 12-09-2016, 07:27 AM
  5. Replies: 1
    Last Post: 05-14-2016, 03:05 PM
  6. Only copying/deleting columns from a table rather than the entire sheet
    By Glade1825 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-01-2015, 09:42 PM
  7. Replies: 2
    Last Post: 11-22-2013, 02:52 PM

Tags for this Thread

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