Copy, then Paste "Value" to the same cell...

[Brad]

I have a column A1 to A100 where names appear in the cells based on a
formula that populates them from another worksheet matching them against
account numbers using the lookup function. So, everything works well except
the other worksheet can change over time and it can foul up the "lookup"
function and post a wrong name, say where one has been deleted.

So, at the end of my existing code, I want to copy cell A1 and paste it as a
value back to itself and then repeat it for all of the cells.

My thanks in advance for any suggestions here.

Brad

 

[Guest]

Sub brad()
Set r = Range("A1:A100")
For Each rr In r
rr.Copy
rr.PasteSpecial Paste:=xlPasteValues
Next
End Sub

 

[Dave Peterson]

with worksheets("sheet999").range("a1:A100")
.value = .value
end with

Is another way.