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

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

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

Sub brad()
Set r = Range("A1:A100")
For Each rr In r
rr.Copy
rr.PasteSpecial Paste:=xlPasteValues
Next
End Sub
--
Gary''s Student - gsnu200728

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

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

Is another way.

Brad wrote:

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

--

Dave Peterson