I use the vlookup on dozens of sheets each day, always returning #n/a
somewhere in the column. So I copy, paste value, then replace #n/a with
nothing.

When I tried to make a macro do do it, instead of the highlighted range on
the new sheet it wanted to find the original sheet and ranges.

What do I need to look at whatever is highligted on the active sheet, copy,
paste value, and replace #n/a with nothing?