Loop through a range, copy cell content to the editor and re-paste content to the same cell

[MrOllyR]

Hi, everyone

I have a workbook with a lot of formulas which I share with colleagues. I can protect these cells but invariably they get broken and require repair. I can create a macro by:

1. Record macro
2. Selecting a cell
3. Copying the formula
4. Re-pasting the formula in the same cell
5. Repeating with other cells in a range
6. Applying a button to repeat the process above when formulas are damaged

The problem with this is it's going to take me a long time to select, copy and paste every cell which has a formula. Is there a macro that can set this up for me without going through the process above?

Thank you in advance.

 

[offthelip]

You could save all the formula on a veryhidden worksheet so that only people familar with vba can get to it, the you can copy the alll formula back in one go. likethis:

Sub tst()
Dim ws As Worksheet
Set ws = ActiveSheet
Application.ScreenUpdating = False
inarr = ws.UsedRange.Formula
Sheets.Add
ActiveSheet.Name = "Hiddenformula"

With Worksheets("Hiddenformula")
 .Range(.Cells(1, 1), .Cells(UBound(inarr, 1), UBound(inarr, 2))) = inarr
End With
ActiveSheet.Visible = xlSheetVeryHidden
ws.Activate
Application.ScreenUpdating = True

End Sub


Sub makevisible()
Worksheets("Hiddenformula").Visible = xlSheetVisible  ' this is to make the hidden sheet visible
End Sub

Sub makeinvisible()
Worksheets("Hiddenformula").Visible = xlSheetVeryHidden  ' and this is to hide it again

End Sub