+ Reply to Thread
Results 1 to 13 of 13

Insert rows based on the current cell value and, then, copy some cells into the new rows

  1. #1
    Registered User
    Join Date
    11-02-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Insert rows based on the current cell value and, then, copy some cells into the new rows

    Hello All,

    I am a fairly experienced Excel user, but new to VBA and macros. I have recorded some macros and done a bit of editing of the VBA code.

    I am trying to create a macro that will, based on the value of the current cell, insert X minus 1 new rows below the current row, where X is the value of the current cell and, then, copy some cells from the current row straight down into the new blank rows.

    For example, if the data is located in the range J10:O13 as follows and J10 is the starting cell:

    ___J__K__L__M__N__O

    10__3__X__X__X__X__X
    11__2__X__X__X__X__X
    12__3__X__X__X__X__X
    13__1__X__X__X__X__X


    The macro would change the data to:

    ___J__K__L__M__N__O

    10__3__X__X__X__X__X
    11_____X__X__X__X__X
    12_____X__X__X__X__X
    13__2__X__X__X__X__X
    14_____X__X__X__X__X
    15__3__X__X__X__X__X
    16_____X__X__X__X__X
    17_____X__X__X__X__X
    18__1__X__X__X__X__X


    Note: I had to include the underscores above just to get the columns to line up. Only the cells in columns K thru O are copied down into the new rows, not the cells in column J.


    I did find the following macro which works perfectly to insert the new rows, but I cannot seem to add the proper code to get the specified cells from the current row to copy down into the new cells:



    Please Login or Register  to view this content.

    I have read that it is better to start at the bottom of the data and move up when inserting rows and I do wonder if that may be related to my problem of not getting the copy/paste to work properly.

    Here is the code that I tried to add for copying and pasting:


    Please Login or Register  to view this content.
    It does copy and paste, but the pasted data keeps shifting one column to the right each time the macro loops.

    Thanks for any help anyone can provide!

    RZ
    Last edited by Excel-RZ; 11-03-2013 at 12:48 PM. Reason: code tags added

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Insert rows based on the current cell value and, then, copy some cells into the new ro

    can you attach a sample file with data and desired result ?
    If solved remember to mark Thread as solved

  3. #3
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Insert rows based on the current cell value and, then, copy some cells into the new ro

    Hi, Excel-RZ,

    please wrap your code in Code-Tags when showing your preocedures here in Exccel-Forum.

    You result is showing an error as row 19 should not be filled.

    Maybe try this code
    Please Login or Register  to view this content.
    @patel45:
    I think both original set as well as result have been given in the opening post.

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Insert rows based on the current cell value and, then, copy some cells into the new ro

    RZ: Welcome to the forum, unfortunately.......

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



    (I have added them for you today. Please read all our rules and abide by them in the future.)
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  5. #5
    Registered User
    Join Date
    11-02-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Insert rows based on the current cell value and, then, copy some cells into the new ro

    Quote Originally Posted by alansidman View Post
    RZ: Welcome to the forum, unfortunately.......

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



    (I have added them for you today. Please read all our rules and abide by them in the future.)

    Sorry about forgetting to add the tags around my code. Thanks for doing it for me. I will do it on all future posts. Thanks Again! RZ

  6. #6
    Registered User
    Join Date
    11-02-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Insert rows based on the current cell value and, then, copy some cells into the new ro

    Quote Originally Posted by patel45 View Post
    can you attach a sample file with data and desired result ?
    Yes.. file attached here. Thanks! RZ

  7. #7
    Registered User
    Join Date
    11-02-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Insert rows based on the current cell value and, then, copy some cells into the new ro

    Quote Originally Posted by HaHoBe View Post
    Hi, Excel-RZ,

    please wrap your code in Code-Tags when showing your preocedures here in Exccel-Forum.

    You result is showing an error as row 19 should not be filled.

    Maybe try this code
    Please Login or Register  to view this content.
    @patel45:
    I think both original set as well as result have been given in the opening post.

    Ciao,
    Holger

    Thanks Holger! Good catch on row #19 being an error in my desired results table. Since the original cell value was "1", there would be no extra row added.

    Also, I tried to run the code that you suggested, but I get a "Run-time Error 13 Type Mismatch". Since I noticed the - 1 Step value, I assume your code was meant for starting the macro from the bottom of column J, so I tried that first and got the error. Then I tried starting it from the top of the column, but I did get the same error. Any suggestions? Thanks again for your help!

  8. #8
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Insert rows based on the current cell value and, then, copy some cells into the new ro

    Hi, Excel-RZ,

    no need to fully quote any of the other posts when you answer to them - you should reduce the quotation to only the necessary parts.

    In the opening post the range starts at Row 10y in the sample you supplied at Row 18 - if you didnīt alter the end of the loop from 10 to 18 to me that explains the run-time error when in Row 16 text is found.This may be avoided by altering the code to

    Please Login or Register  to view this content.
    Ciao,
    Holger

  9. #9
    Registered User
    Join Date
    11-02-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Insert rows based on the current cell value and, then, copy some cells into the new ro

    Wow! That works great Holger! Thank you very much for the solution and your rapid replies! Look like it is an absolute macro and not relative to where the cursor is in the active sheet, so, in my actual data set, I will just need to adjust the column and cell references to be appropriate for that data, correct? What I had supplied was just a sample so that I could show what I needed. I think I should be able to easily make this work on my real data. Thanks Again! RZ

  10. #10
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Insert rows based on the current cell value and, then, copy some cells into the new ro

    Hi, Excel-RZ,

    the amendments could be made in the 2 constants at the begin of the code:
    Please Login or Register  to view this content.
    You may think about getting the column number using Application.Inputbox(type:=8) for a range but you would need to adapt the code slightly (range wants a string as the first part while Cells will work with both strings and longs for Columns).

    The Offset must be adapted if other than the next five columns should be copied or more or less than five columns.

    Ciao,
    Holger

  11. #11
    Registered User
    Join Date
    11-02-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Insert rows based on the current cell value and, then, copy some cells into the new ro

    Hello Holger,

    One more thing (I hope)... I just applied your code to my real data set and it worked! Yay! One improvement I need to make is that the cells that are being copied have just the values of the original cells and not the formulas. Sorry, I did not mention before that some of my source cells contain formulas. Normally, this is a very easy thing to change when pasting, as you just select values only or formats only or just paste the whole contents and everything goes, including formulas. In this case, it is not clear to me what part of the VBA code I would change to make it copy all cell attributes (formulas, formats and values). Would you please advise me on how to change the code to get the formulas to copy as well? Thanks for you help again! RZ

  12. #12
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Insert rows based on the current cell value and, then, copy some cells into the new ro

    Hi, Excel-RZ,

    from the last posted code change the code line
    Please Login or Register  to view this content.
    to read
    Please Login or Register  to view this content.
    Ciao,
    Holger

  13. #13
    Registered User
    Join Date
    11-02-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Insert rows based on the current cell value and, then, copy some cells into the new ro

    That works great! Thanks again for all your help! How cool is this (for me anyway)... collaborating with a guy in Germany to get my Excel challenged solve... My family 6 generations back was from Baden Baden. Thanks again Holger! I will mark this thread as solved. (..as soon as I can figure out how to do that.) Ciao! RZ

+ 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] insert rows based on number in cell and copy the data down into the new rows
    By pziefle in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 05-05-2013, 11:19 AM
  2. Insert rows based on a value in a cell and copy the data down into the new rows
    By eyeman96 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-05-2013, 07:54 AM
  3. 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
  4. Insert a number of rows based on a cell value and copy formulas on multiple worksheets
    By Charleneq40 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-08-2012, 12:45 PM
  5. [SOLVED] Insert Multiple Rows Based Off Number in Cell and Copy Data From Above New Rows
    By tstell1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-02-2012, 04:15 PM

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