Restore formulas macro

[JYTS]

Hey guys, I need a (fairly simple methinks) macro to restore formulas in a range upon a button press. The formulas will be overwritten by the user, but eventually will need to be restored. See the attached workbook and please let me know if you have any questions.

Thanks in advance!

Restore formula problem.xlsx

[ben_hensel]

I would just use MacroRecorder to record myself manually fixing the table, and then bind the recorded macro to the button.

[TMS]

Please Login or Register  to view this content.

Regards, TMS

[JYTS]

Thanks to both of you. I got both methods to work, but I just realized my real situation is more complicated. The formulas are array formulas - and not ones that are linked. So Range("C4:C" & lLR).FormulaArray won't work for me because it makes a set of cells that can't be individually edited.

Can you alter the macro to insert array formulas that are not linked? Thanks!

[TMS]

Please Login or Register  to view this content.

[Bernie Deitrick]

Just use copy and paste - as long as the formula in C4 is intact:

Range("C4").Copy Range("C4:C" & lLR)

[TMS]

If you use

Please Login or Register  to view this content.

it will fail because you can't change part of an array.

If you use

Please Login or Register  to view this content.

it will work but it will copy the cell formatting, including the borders.

[JYTS]

Thanks guys. TMS, how would you repeat this across a number of sheets? For example restore formulas on Sheet1, Sheet2, Sheet3, etc. The exact same formula, and the same range on each sheet?

Bernie, how do you modify that to just copy formulas? (I'm getting formatting copied...). EDIT: Nevermind, based on TMS's comment I don't think that will work.

Thanks

[TMS]

See post #5 and post #7

[TMS]

I love it when the goal posts start to move ...

[JYTS]

??? I really don't know any VBA but I don't think the post #5 is going to work across numerous sheets. Or #7...


Thanks though +1.

[TMS]

... but I don't think the post #5 is going to work across numerous sheets

No, it wasn't designed to. When you started the thread, it was a simple formula being inserted into a range of cells on a single sheet. It's late now, I'll look again tomorrow.

Thanks for the rep.

[Bernie Deitrick]

If you have specific sheets, with the same source cell:

Please Login or Register  to view this content.

If you want to do all sheets, with the same source cells

Please Login or Register  to view this content.

[JYTS]

Alright thanks TMS and Bernie - and sorry for moving the goalposts!

[TMS]

You're welcome. Thanks for the rep.

No worries about the goalposts I just tend to highlight it so people are aware that it is often better to think through their actual and total requirement ... and post a sample workbook that truly reflects the final outcome.


I thought I had posted this earlier, but, apparently not. Not sure what happened there. And, what was worse, I hadn't saved a copy

Anyway, here it is for completeness:

Please Login or Register  to view this content.

See the updated sample workbook.

Regards, TMS

[JYTS]

Great - thank you for posting that. I'm beginning to learn VBA and it's great to see all the ways you can skin a cat – and compare the logic behind them. Again, thanks for following up TMS.

[TMS]

You're very welcome. Enjoy