Need to have result from a formula in one cell appear in another cell...

[danielneedssomehelp]

I have a formula in A1 (it references about 100 different cells), and I'd like the result of A1 to appear in A2 without A2 containing a formula.

There might be a REALLY simple solution to this though without reading the stuff below. I'm not opposed to locking A2, but when I do that, somehow the ability to copy the contents in A2 doesn't work. Maybe it's because I have checkboxes on my spreadsheet or there's a macro... I have no idea. But if there were a way to copy the results from A2 while that cell is locked, then that is all I would need. I could unlock that cell, but then there's the problem of the formula appearing if you double click the cell, and then all the stuff below...

If I can't copy the contents of a locked then, then I could unlock the cell and use the following macro, but then that creates other problems....

Here's the macro:

Private Sub Worksheet_Change (ByVal Target As Range)
Range("CellWithValue").Value = Range("CellWithFormula").Value
End Sub

In theory, this code works great because any time there is a change to the worksheet, the output in A2 always updates. Here's is my problem... I have a command button macro which deletes about 200 fields on the worksheet when clicked, but when I use the code above, it takes a while for all the fields to be cleared because every time a field is cleared it counts as a change and then the code above runs 200 times, which in turn increases the time it takes for the command button to run and clear all the fields.

So I need a macro or something that hardcodes the results in A1 to A2, and it must be dynamic so that if there is a change on the worksheet, A2 is updated, BUT I want to keep my "clear all fields" command button without it running so slowly.

Here's my idea: if you click on the "clear all" command button, it clears all fields, but only the last field cleared is identified by the code above as a change. So the "clear all" button clears all fields, and isn't slowed down by the code above.

Any idea on how to do that?