Insert New Row if needed

  • Hello,


    First of all I have posted the same thread at mrexcel https://www.mrexcel.com/board/…ew-row-if-needed.1198559/

    And here is my question and needed help.


    First of all I need to say thank you to all of you that have helped me and write (tried and did) the below code.

    The code is working perfectly!. What the code do is that it collect data from 3 or more sheets and insert them to another sheet in order to create an invoice. So far , so good.

    The problem is that there is an alert if the inserted data are more than row 70 to end sub. Now there is the need to incremental add rows to invoice and not checking if they are more than 70.

    Starts from Row 15 and add row if there is a need (ie retrieve more data from sheets and copy them). I have tried to make it happen using a tutorial from VBA Macro to Insert Row in Excel Based on https://www.exceldemy.com/exce…rt-row-based-on-criteria/ but no luck.

    Also the row must have columns A B C and D has a formula (B*C) .Can someone please help to make this work?


    You can find at the example workbook. The module needed to change is Invoice



    Thank you!


  • royUK Ok I uploaded the file as .zip and also uploaded it at Google Drive


    EXAMPLE.xlsm
    Dimmer Stock,EXAMPLE PRODUCTS,QUANTITY,PRICE 2,PRODUCT 1,1,€2,00 5,PRODUCT 2,6,€9,00 8,PRODUCT 3,11,€16,00 11,PRODUCT 4,16,€23,00 14,PRODUCT 5,21,€30,00…
    docs.google.com


    (I also noticed I became a Student! yes I like that because I have learned a lot of things in this forum)

  • If you formatted the Invoice data as a Table then you wouldn't need VBA, a new Row would be added automatically as you complete a row.


    Overview of Excel Tables.

  • Thank you for the info royUK. I really didnt think of that.

    I will try that later and be back.

    The new row will be blank yes? If I need at column D lets say to sum B+C do I have to place the formula manual? Or there is a way to do it automatically?

    And do I have to delete the check (if n>70) from the code, in order to make table work?

  • Basically what I want to do is

    FORMA sheet has only the below lines as shown in image 1

    For example I need to add 20 products (20 rows) but I only have one free row , the starting row 15.

    Then the script to automatically add 19 more rows but keep rows 16-21 (the last 5) untouched as they needed for the totals. So the totals will start at row 36 and rows 15-35 will be filled with data from other sheets. Finally the addedd row will need to have the formula in column D.

    So need to always add more rows depending the amount of products collected from other sheets, not delete the last 5 rows but "push" them down.

    I hope I explain it correct. Than you for your effort royUK !

  • royUK I tried the solution with the table, but I may do something wrong. It doesnt push down rows and add a new one but it is overwrite the last rows to continue script. My problem is that I dont to overwrite the last 5 rows, but add more rows and continue insert data. I really hope you understand, I know I am getting as tedious and sorry about that, my I am very close to finish this (lets say) project. Thank again.

  • I don't understand. Are you using VBA to add rows to the Table?


    How are you inputting lines into the invoice?

    Ahhh ! I am sorry . I dont! at the original code there is a limit at 60 rows (including headers etc) . and if they go above Row 70 then ends the script. Because now the items will be many more, this means a lot of rows then I dont need the limit but add a new row if needed. I tried table but no luck! From my previous post


    For example I need to add 20 products (20 rows) but I only have one free row , the starting row 15.

    Then the script to automatically add 19 more rows but keep rows 16-21 (the last 5) untouched as they needed for the totals. So the totals will start at row 36 and rows 15-35 will be filled with data from other sheets. Finally the addedd row will need to have the formula in column D.

    So need to always add more rows depending the amount of products collected from other sheets, not delete the last 5 rows but "push" them down, not overwrite anythink just add rows.


    I need rows 1-14 stable and rows 16-21 be stable , add rows between them. I hope this is clear...

  • You haven't answered my question about how you are actually adding the data to the invoice.

    By collecting them from Column D from the other sheets of the workgroup. If a product has quantity I collect Quantity and Price and insert them in the FORMA sheet with Description, Price and Quantity. using VBA.


  • The idea of a Table is that you don't have all those unused rows in the invoice. The code should simply copy the required data to the Table using the Tables features.


    Attach your current workbook.

  • royUK I managed to create the table with the formulas. I have some cell merged thats why it didtnt work. My problem is that I need the last 5 rows of the "original" invoice to stay untouched, so the sums will be last. I cant move rows down and not overwrite the rows that are already there.

    Is this possible? To have some rows always there and not be overwriten ? And can this be done with VBA?

    I hope you understand, my English are not that good as I am not English. Thank you !

  • Here is the workbook at google drive. I still cant upload xlsm here


    EXAMPLE1.xlsm
    Dimmer Stock,EXAMPLE PRODUCTS,QUANTITY,PRICE 2,PRODUCT 1,1,€2,00 5,PRODUCT 2,6,€9,00 8,PRODUCT 3,11,€16,00 11,PRODUCT 4,16,€23,00 14,PRODUCT 5,21,€30,00…
    docs.google.com

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!