I have a spreadsheet where Sheet1, Column L has a formula to calculate a budget. Once the budget is set, I recorded a macro and assigned to a FormControl button to remove the formulas to "freeze" the budget. The macro basically copies the column, pastes the column (formulas included) into Sheet2 and then pastes only the values back into Sheet1, Column L.
There is also a reset button, if you want to "unfreeze" the budget and recalculate. It copies the column from Sheet2 and pastes back into Sheet1 to reinstate the formulas.This recorded macro is also assigned to a FormControl button if that makes a difference.
The problem I'm having is that if the user inserts new rows into the table in Sheet1 AFTER the budget has been frozen, when they click the reset button, the cells in the copied Column L in Sheet2 no longer align after any inserted rows.
Is this something that can be done with VBA without using the copy&paste route to avoid the inserting rows issue?
To make it a little more confusing, the column contains 2 formulas. First one is in the majority of cells and the second is only when there is a "Subtotal" in the adjacent cell in Column K. The range goes from row 4 to row 52.
There is also a reset button, if you want to "unfreeze" the budget and recalculate. It copies the column from Sheet2 and pastes back into Sheet1 to reinstate the formulas.This recorded macro is also assigned to a FormControl button if that makes a difference.
The problem I'm having is that if the user inserts new rows into the table in Sheet1 AFTER the budget has been frozen, when they click the reset button, the cells in the copied Column L in Sheet2 no longer align after any inserted rows.
Is this something that can be done with VBA without using the copy&paste route to avoid the inserting rows issue?
To make it a little more confusing, the column contains 2 formulas. First one is in the majority of cells and the second is only when there is a "Subtotal" in the adjacent cell in Column K. The range goes from row 4 to row 52.
Excel Formula:
=IF(AND(F8="Weeks",J8="Per Day"),H8*I8*K8*7,I8*K8)
Excel Formula:
=SUMIFS(L8:L12,$K$8:$K$12,"<>Subtotal")
Last edited:
