Insert row with formulas

[ashacooper]

Hi there,

I have the following spreadsheet that has several sheets and will require the user to keep adding sheets between the the 2 red "do not delete" sheets. I want the user to be able to add a row along with the formulas in place. I have used a macro from previous threads but what happens when I insert a row in the middle of other rows, the sum formula in column e on the last sheet copies the cell above and does not follow the formula. All other formulas are ok. How can this be fixed.

[dilipandey]

Hi ashacooper,

Welcome to the forum.

I have gone through your attachment and did not get everything like.. in which sheet you are inserting row in the middle of other rows.. is it Month ?
The formula in column e is "=IF(OR(D7="",C7=""),"",INT(D7-C7+1))" and if you insert any of the sheet anywhere, it will have no impact. Would request you to please elaborate more on this. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[ashacooper]

Hi Dilipandey,

Thanks for your reply. The problem is in the "Charged Summary" sheet. If you insert 2 rows using the ïnsertrows" macro, the try to insert another row in the middle of these rows using the same macro the forula doesnt flow down, it copys the same cell above. The word doc attached shows that in cell E9 the formula refers to cell E8 not E9 as it should be. Also, when you the delete this newly inserted row, error references appear as shown. I hope this is now clear. If not please let me know. PS - all sheets must be selected (if there was a way that this macro could work without having to select all sheets all the time the that would be great).

Regards
7.docx

[dilipandey]

Hi ashacooper,

I am not able to find any macro on the attachment text.xlsx
Also it is quite obvious if you delete any of the row which is in reference with any formula, an error (#REF) will be shown, so you need to be cautious on this.

Regards,
DILIPandey

<click on below 'star' if this helps>

[ashacooper]

Hi Dilipandey,

Not sure why the macro isnt showing. Does on mine. Here it is.

Please Login or Register  to view this content.

[dilipandey]

Hi ashacooper,

One reason as to why macro is not working because in advanced version of Excel, you need to save the file with extension xlsm.
I have used macro "InsertRowsAndFillFormulas_caller" on the last right side sheet (Charged Summary) and encountered no errors after inserting rows. See the attachment.

Regards,
DILIPande

<click on below 'star' if this helps>

[ashacooper]

Hi Dilipandey,

I dont have excel on me at the moment and wont til tomorrow. Did you then insert another row in between those you just created and then looked at the formula? It works fine when inserting initial rows under each other but doesnt when you insert one inbetween.

[royUK]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[ashacooper]

Sorry,

Will do in future. Anyone able to help me out with this is issue?

[royUK]

You haven't used Code Tags

[ashacooper]

Code tags added