+ Reply to Thread
Results 1 to 15 of 15

SOLVED! copying rows and inserting them while keeping formulas/formatting by vba

  1. #1
    Registered User
    Join Date
    10-15-2013
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    SOLVED! copying rows and inserting them while keeping formulas/formatting by vba

    Hello,
    I have this code but it is not working for me and I am wondering if you can help.

    I want to copy a particular row (2 rows) and insert it in any place I want still keeping the formulas and formatting by the click of a command button. I have the creation of the command button down but the code is giving me a headache. Please can you see where I am wrong and correct it for me please?



    Please Login or Register  to view this content.

    Thanks
    Last edited by alansidman; 06-17-2020 at 02:12 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    576

    Re: copying rows and inserting them in any place while keeping formulas/formatting by vba

    Maybe try something like this :
    Please Login or Register  to view this content.
    2020-06-17_03-30-07.gif

    The code assumes that your row 2 (which has formula and formatting) you want to copy is from column A to C.
    Then it ask you to select the range of rows in column A.
    If for example you want to insert it below cell A3 then you start to drag from cell A4 down to any row number.
    Last edited by karmapala; 06-16-2020 at 03:30 PM.

  3. #3
    Registered User
    Join Date
    10-15-2013
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: copying rows and inserting them in any place while keeping formulas/formatting by vba

    Hello karmapala,
    Thank you so much for your input.

    the formulas are on two rows so I want to copy that two rows (rows 1 and 2) and should be able to to paste them on any rows of my choosing and still maintain the formulas and formatting.

    Really appreciating your help on this

  4. #4
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    576

    Re: copying rows and inserting them in any place while keeping formulas/formatting by vba

    Hi Fredvonny,

    please change
    Please Login or Register  to view this content.
    into
    Please Login or Register  to view this content.
    Please remember, since there are two rows which copied,
    then when you select the range it's better in an even number...
    for example 2 or 4 or 16 or 28 or 10 rows, etc

    2020-06-17_03-36-31.gif

  5. #5
    Registered User
    Join Date
    10-15-2013
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: copying rows and inserting them in any place while keeping formulas/formatting by vba

    Thank you so much karmapala it worked. I really appreciate your help! you are awesome!

    Just a thought. is there a way to just use a number in the input box to insert the rows?

    Again, thank you so much

  6. #6
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    576

    Re: copying rows and inserting them in any place while keeping formulas/formatting by vba

    Quote Originally Posted by Fredvonny View Post
    Just a thought. is there a way to just use a number in the input box to insert the rows?
    Yes .

    Please Login or Register  to view this content.
    Please remember the answer to "how many rows ?" should be an even number.
    Also, I assume that what you want is the inserting cells, not inserting entire row.
    For inserting entire row, it would be different code.

    thank you so much
    You're welcome, Fredvonny.
    Glad I can help.
    Last edited by karmapala; 06-16-2020 at 04:51 PM.

  7. #7
    Registered User
    Join Date
    10-15-2013
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: copying rows and inserting them in any place while keeping formulas/formatting by vba

    Wow!!! another brilliant piece. You are teaching me a lot and I am so so so grateful. If it will not be too much work, can you give me the code to copy the entire row and insert?

    Thank you so very much!

  8. #8
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    576

    Re: copying rows and inserting them in any place while keeping formulas/formatting by vba

    Quote Originally Posted by Fredvonny View Post
    the code to copy the entire row and insert?
    I limit from column A to Z row 1 and 2 as the range to be copied.
    The sub below will insert the entire row as many as the answer for "how many rows ?"

    Please Login or Register  to view this content.
    Just limit it according to your last column data.
    If for example the last column of your data is column ABC,
    then change the Z2 to ABC2 in the line Range("A1:Z2").Copy
    Last edited by karmapala; 06-16-2020 at 05:34 PM.

  9. #9
    Registered User
    Join Date
    10-15-2013
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: copying rows and inserting them in any place while keeping formulas/formatting by vba

    Hello karmapala
    I cannot even express my profiund gratitude for your help. Thank you!

    with the last code, it is every thing that i need but it does not copy so it does not paste. It will insert the blank rows at the right place but nothing will paste. please can you take a look for me?

    Thank you soo much

  10. #10
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    576

    Re: copying rows and inserting them in any place while keeping formulas/formatting by vba

    Hi Fredvonny,
    I think it would be better if you can attach a sample workbook,
    so we can have a look on how is the data of your situation in the sheet.

    BTW, I've just found out that if copying the entire row 1 and row 2,
    the code can be shorter :

    Please Login or Register  to view this content.
    Still need to see how is your data though,
    so we can have a look what cause it doesn't do the copy/paste, only the insert.
    Last edited by karmapala; 06-17-2020 at 07:17 AM.

  11. #11
    Registered User
    Join Date
    10-15-2013
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: copying rows and inserting them in any place while keeping formulas/formatting by vba

    Hello karmapala,
    Thank you once again. I am sorry for the back and forth. Please see attached a copy of my work. The Tab A. This is the form I use to input countless of data. all I am trying to do is after all the lines are used, I just click a button and another entry lines are added either at the bottom or at any where I choose to add. Thank you for your help. I salute and appreciate you for your help
    Attached Files Attached Files

  12. #12
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    576

    Re: copying rows and inserting them in any place while keeping formulas/formatting by vba

    Fredvonny,

    I've just looked into your sample workbook.
    I'm very sorry, the code I gave you is not meant to work with that kind of situation like in your sample workbook .

    First, I don't quite understand what do you mean that you only want to copy the row 1 and row 2,
    since in row 1 and row 2 on sheet "Form" of your sample workbook only contains information and no formula in there.
    As seen in the image below :
    EXCEL_2020-06-17_23-04-35.png

    Second, your sheet has a merge cell,
    this sometimes can get unpredictable result when run a macro.
    I've read some articles I've found in the internet who advice to avoid use a merge cell as much as possible.
    For example in this article.

    The image below is the result after I run the macro in your sample workbook.
    I need to "carefully" choose the start of the row number (I put number 12) which must be the the first row number of the merged cell.
    EXCEL_2020-06-17_23-14-48.png

    The above image, the orange and green highlighted is the result after I put 12 as the row number to start the insertion, and put 4 as for the answer of "how many rows?"
    .
    .
    .
    .
    .
    If I just put the start of the row number "carelessly" (I put number 9) which turn out it's the second row number of the merged cell,
    the insert process work, but the copy/paste process doesn't work ... as seen in the image below.
    EXCEL_2020-06-17_23-20-22.png

    As you can see. because I put number 9 to start and row 9 is the second row of the two merged rows (column A), and put 4 as the answer of "how many rows?",
    the code did insert four rows (seen in purple color), and the merged cell now expand (the grey color), and the code unable to paste to the insertion area.


    Still, I don't understand how do you want the result.
    If you have time... please make a new sheet, and by doing it manually,
    put the kind of result that you expected after you copy row 1 and row 2,
    then insert for three times in any row below it for example.

    Anyway, I try to guess what you want....
    Assumes that the beginning condition (before running the macro) of sheet Form is like this :
    EXCEL_2020-06-17_23-36-53.png



    Then after running the macro, you want the result like the below image if you input 2 as the answer of "how many rows?"
    EXCEL_2020-06-17_23-38-26.png


    So, before running the macro, the last row of the Form is row 15.
    After running the macro, the last row of the Form now is row 17.

    If that's what you mean, then the code is like this :
    Please Login or Register  to view this content.
    For the code "know" what is the last row number (not including the row number for total calculation, control total, etc) of the Form,
    it find first the word "Total" in column J, because one row above the cell with value "Total" is assumed will always be the last row number of the Form.
    So please be sure that there will be only one word "Total" in one cell of the whole column J.

    So, in the "beginning" state of the form, the code find that the last two number of the row is row 14 and 15.
    That two rows (row 14 and 15) are copied then inserted right below row 15.

    That's just my guess on what you want .
    Last edited by karmapala; 06-17-2020 at 12:10 PM.

  13. #13
    Registered User
    Join Date
    10-15-2013
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2010
    Posts
    8

    SOLVED! copying rows n inserting them while keeping formulas/formatting by vba

    WOW!!! WOW!!! WOW!!!! Are you a mind reader??? This is exactly what I want! sorry for confusing you with the first two rows. I was thinking about the first two rows where the data will be filled and you figured it out. I am soooooo grateful! Thank you thank you thank you.

    A very grateful Friend
    Fredvonny
    Last edited by Fredvonny; 06-17-2020 at 01:27 PM.

  14. #14
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    576

    Re: SOLVED! copying rows n inserting them while keeping formulas/formatting by vba

    Fredvonny,

    You're welcome.
    Glad that finally I can help .

  15. #15
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,554

    Re: SOLVED! copying rows and inserting them while keeping formulas/formatting by vba

    Code Tags Added
    Your post does not comply with Rule 2 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 at http://www.excelforum.com/forum-rule...rum-rules.html



    (I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

+ 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. inserting rows with formulas and formatting
    By dtp81390 in forum Excel General
    Replies: 5
    Last Post: 05-09-2016, 12:28 PM
  2. copying rows, while keeping formulas on orginal
    By scott micklo in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 12-29-2015, 05:25 PM
  3. Replies: 3
    Last Post: 04-11-2013, 01:29 PM
  4. Copying Values but keeping rows that have formulas in them
    By amartino44 in forum Excel General
    Replies: 1
    Last Post: 01-01-2013, 04:44 PM
  5. Inserting new Rows & copying formulas in protected sheet
    By amlal in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-24-2012, 11:19 AM
  6. Macro for copying formulas + inserting rows
    By Marco-Kun in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-25-2009, 09:41 PM
  7. Help with keeping formulas in place!
    By tapuza in forum Excel General
    Replies: 3
    Last Post: 12-23-2005, 03:25 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