+ Reply to Thread
Results 1 to 6 of 6

VBA script to insert new rows based on data in other cells, then cut/paste into new row

  1. #1
    Registered User
    Join Date
    03-15-2021
    Location
    New York, NY
    MS-Off Ver
    Office 2013
    Posts
    4

    VBA script to insert new rows based on data in other cells, then cut/paste into new row

    Hi, I have a spreadsheet that contains mailing list data. It's in a format in which there are Alt1Email (column R), Alt2Email (column S), Alt3Email (column T), and Alt4Email (column U). Wanna cut and paste that data from those columns to the Email column (column Q) in NEW ROWs based on how many AltEmails the contact has. Then ultimately delete columns R,S,T, and U. I thought this out in a plain logic script (below) but being new to VBA I'm having trouble understand syntax a bit. Please if anyone can assist would greatly appreciate. Kind regards! Attached is the spreadsheet and screenshots.. Thanks!

    ----------

    {If cells in columns R,S,T,U are all Populated (not null) in the row the VBA script is working on... first row being row TWO... so R2,S2,T2,U2)

    then

    INSERT FOUR new rows BELOW this row

    AND

    COPY from that "reference" row the cell values from columns A thru P... to the corresponding columns of the 4 new rows just created,

    AND

    CUT cell value from column R AND PASTE IT to the FIRST new row immediately under the original "reference" row into COLUMN Q

    AND

    CUT column S value and paste to SECOND new row's Q column

    AND

    CUT column T value and paste to THIRD new row's Q column

    AND

    CUT column U value and paste to FOURTH new row's Q column}

    OR

    {If out of R,S,T,U.... only columns R,S, & T (only 3 alt-emails) are populated in the current "reference" row

    then

    INSERT THREE new rows below this row

    AND

    COPY from that "reference" row the cell values from columns A thru P... and paste into to the corresponding columns of the THREE new rows just created,

    AND

    CUT cell value from column R AND PASTE IT to the FIRST new row immediedly under the original "reference" row

    INTO COLUMN Q

    AND

    CUT column S value and paste to SECOND new row's Q column

    AND

    CUT column T value and paste to THIRD new row's Q column}

    OR

    {If out of R,S,T,U, only R & S (only 2 alt-emails) are populated in that "reference" row

    then

    INSERT TWO new rows below the reference row

    AND

    copy from that "reference" row the cell values from columns A thru P... and PASTE to the corresponding columns of the THREE new rows just created,

    AND

    CUT cell value from column R AND PASTE IT to the FIRST new row immediedly under the original "reference" row to COLUMN Q

    AND

    CUT column S value and Paste to SECOND new row's Q column}

    OR

    {If out of R,S,T, U, only column R is populated in that particular row)

    then

    INSERT ONE new row below the reference row

    AND COPY from that "reference" row the cell values from columns A thru P... and paste into to the corresponding columns of the THREE new rows just created,




    AND

    CUT cell value from column R AND PASTE IT to the FIRST new row immediedly under the original "reference" row into COLUMN Q}

    {If we just added 4 new rows, move down FIVE rows from the original row... if this new “reference” row is a totally blank row, END this script,

    If not, make THIS row the new “reference” row AND go back to beginning of script

    if we just added 3 new rows, move down FOUR rows from the original row... if this is a totally blank row, END this script

    If not, make THIS row the new “reference” row AND go back to beginning of script

    {if we just added 2 new rows, move down THREE rows from the original row... if this is a totally blank row, END this script,

    If not, make THIS row the new “reference” row AND go back to beginning of script}

    {if we just added 1 new row, move down 2 rows from the original row... if this is a totally black row, END this script, if not, make THIS row the new criteria row}

    {if we added no new rows in the above process, move down to the next row below original row. if this is a totally BLANK NULL row, END this script,}

    Go back to beginning criteria of this script

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,564

    Post Hi, try this !

    (removed 'cause of wild cross posting …)
    Last edited by Marc L; 03-16-2021 at 11:02 PM.

  3. #3
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    4,598

    Re: VBA script to insert new rows based on data in other cells, then cut/paste into new ro

    Try on a copy of your workbook first.

    Removed code because of cross posting. Forum Rules #3 for you to read
    Waiting for OP to acknowledge.
    Last edited by jolivanes; 03-17-2021 at 01:20 AM. Reason: Eliminate Copy & Paste

  4. #4
    Registered User
    Join Date
    03-15-2021
    Location
    New York, NY
    MS-Off Ver
    Office 2013
    Posts
    4

    Re: VBA script to insert new rows based on data in other cells, then cut/paste into new ro

    Wow! Thanks for your script Jolivanes and for your prompt reply! It works perfectly! Even on larger spreadsheet with 2000+ rows. Very much appreciated. Will take a look at your script so I can learn how you did this! And will post your reply on other forums where I posted. Many thanks!
    Last edited by mbnnyc; 03-17-2021 at 01:35 AM. Reason: SOLVED!

  5. #5
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    4,598

    Re: VBA script to insert new rows based on data in other cells, then cut/paste into new ro

    The idea is for you to put links in ALL sites where you posted to ALL posts so people looking for an answer are helped also.
    So if you posted in 5 other sites, have 5 links showing.
    Since you got the code before I realized what Marc L's post meant and deleted it, I post it here just in case it is beneficial to others.

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    03-15-2021
    Location
    New York, NY
    MS-Off Ver
    Office 2013
    Posts
    4

    Re: VBA script to insert new rows based on data in other cells, then cut/paste into new ro

    Posted your solution to the other forums I posted to. Thanks again for your quick and awesome support! Excel forum won't let me post other links now for some reason. Let me put spaces in them

    mrexcel dot com slash board/threads/vba-script-to-insert-new-rows-based-on-data-in-other-cells-then-cut-data-from-those-cells-and-paste-into-those-new-rows.1165153/
    ozgrid.com dot com slash forum/index.php?thread/1229267-vba-script-to-insert-new-rows-based-on-data-in-other-cells-then-cut-data-from-th/
    vbaexpress dot com slash forum/showthread.php?68549-VBA-script-to-insert-new-rows-based-on-data-in-other-cells-then-rearrange-data
    myonlinetraininghubexcel-forum/vba-macros/vba-script-to-insert-new-rows-based-on-data-in-other-cells-then-cut-data-from-those-cells-and-paste-into-those-new-rows#p19608
    vbforums dot com slash showthread.php?890983-VBA-script-to-insert-new-rows-based-on-data-in-other-cells-then-cut-data-from-thos&p=5514690#post5514690

+ 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. [SOLVED] Copy Row Data & Insert 3 Rows & Paste Copied Data In Those Rows
    By TB12 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-31-2020, 12:35 PM
  2. Insert set number of rows between data and copy and paste data above empty rows
    By Orionm45 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-21-2017, 11:35 AM
  3. Copy cells with data - paste (insert rows) to next sheet.
    By BPSJACK in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-25-2016, 01:28 PM
  4. [SOLVED] Insert number of rows with data to new sheets based on values in cells
    By keithtran12 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-28-2013, 11:57 AM
  5. Excel Macro to insert two rows based on condition and copy and paste multiple cells
    By mannabhalo in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-13-2012, 12:56 PM
  6. VBA Script to create new rows based on delimited data
    By aaronic in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-10-2012, 06:20 PM
  7. Copy paste data, insert rows, insert data and change . to -
    By xWiZardx in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-16-2010, 01:44 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