Copy and Paste values but not formulas

[Samhill62]

I have a spreadsheet that has two columns of formulas and following a change to certain other cells within the worksheet, return a prescribed value.
I am looking for some vba code that will automatically freeze the calculated data in the cells without affecting any of the other formula when the workbook is closed.
Does anyone have any similar code they may be able to share please?

 

[Herakles]

Range("A1:A100").value = Range("A1:A100").value

Will, for example, retain the calculated values in the A1:A100 range and overwrite the existing formula.

Let me know if this will give you what you want.

 

[Samhill62]

Hi Herakles, thanks for the speedy response. I too tried the exact solution you gave me but unfortunately it clears the formula from the rest of the cells in the Column.

 

[Herakles]

Can you post the code that you used?

 

[Samhill62]

Can you post the code that you used?

Sub Next1()
Range("A1:A100").Value = Range("A1:A100").Value
End Sub

it's very simple and does what it says on the tin.

 

[Herakles]

The A1:A100 range was only an example I used.

You need to substitute it with your range.

What range do you want to use?

 

[Samhill62]

The A1:A100 range was only an example I used.

You need to substitute it with your range.

What range do you want to use?

A1:A100 funnily enough.

 

[Herakles]

When you say that it clears the formula in the other cells in the column, what does it relpace it with?

Does it do what you want for the A1:A100 range?

 

[Samhill62]

When you say that it clears the formula in the other cells in the column, what does it relpace it with?

Does it do what you want for the A1:A100 range?

It completely clears the formula from the rest of the cells to the bottom of column A, deleting the formula.

 

[Herakles]

It works for me so I don't know what else is happening on your sheet.