Posts by jeoffrey3

    I have a macro that copy and paste rows, based on a input userbox. Those rows contain formulas but, once pasting them, they become incorrect (problem of references). I'd like to make the formulas dynamic in the code (via R1C1) or via Index function so that inserting news rows, results don't change. As you can see on the picture, some cells are refering to another sheet, some are in absolute / relative references, but at the end, only rows #19 and #20 contain formulas.

    How can I adapt the code below?


    I've created a thread on this website as well: https://stackoverflow.com/ques…serting-new-rows#61101725


    Thanks !! :)

    Files

    Hello everyone!


    I have a macro that add news rows based on a input userbox and I'd like to use the autofill to keep the formulas and also same layout if possible.

    Example: I want to add 4 extra products in sheet1. Now, the macro (my button) creates 4 new rows below row #24 (Product1), row #28, and row #32. I want to apply the original formulas in columns O, AB, AO, BB and BO to the new inserted rows. How can I adapt the following code?


    Besides, I want to do the exact same thing on sheet2, but in which values of cells are the same as sheet1 (as already set)

    Thanks for your help!

    Hi Justin,


    Thanks a lot for this! :) I'll use that as a basis, putting at first 1 row then, if needed, the user will add the extra rows via the macro.


    May I ask you to indicate me in the code the following:


    - To keep the formulas (SUM) in the columns O, AB, AO and such (white cells) and


    - To keep the same layout as well (lines around the cells, etc..?


    And finally, how can I get the code to do the exact same thing on the sheet2 (setting up the same rows and layout as above) but where values in cells are just equal to (=) cells from sheet1?

    Hi, thanks for your return.


    Assuming I want to add 4 extra products or services (column A) between row #24 and row #29, I want those same new rows to be reflected between rows #33 and #38, #42 and #47, #57 and #62 and finally #66 and #71.


    Other option could be to add just after the initial products or services so as from row #29 or juste before row #24, but this wouldn't solve the issue of removing rows manually if I have only 2 products or services.


    Since this file will be used for different target groups, it's difficult to anticipate the number of rows per section...


    Hope this is clearer! :)

    Hello everyone! I'm back on the forum since I need to dig into some VBA code (or not) that could help me on the following issue:

    I want to create a textbox message where the user needs to give an input (= number of different products or services), then, macro will create/remove rows according this input on sheet1 and sheet2, for 3 different sections (volumes, unit prices & turnover).

    Note that in sheet 1: column O is a formula, last year goes to 2025 (column BO). While sheet2: is refering directly to sheet1 (simply = the cell), same structure

    The idea is then to avoid to complete manually the information when adding or removing products / service lines.

    I hope it's clear, but don't hesitate to ask me for more info..

    How can I start with the code?

    Thanks a lot!