vlookup and return entire cell including formattin

Question

I'm looking to vlookup a value from a tableArray on a different workbook and return the value and entire cells content.

This SO answer is nearly what I'm looking for.

If I amend this links copyFormatting routine (Private Sub copyFormatting(destCell As Range, srcCell As Range)) to copy the cell and paste all instead of formatting the cells font:

srcCell.Copy
destCell.PasteSpecial xlPasteAll

Then the code works as is when the return value is within the same workbook.

Any ideas how I can get the set range line -

fromCell.Parent.Range(destAddr)

to work when referencing a cell address in another workbooks sheet?

Answer 1

Set extractDestRange = Range(destAddr) 'fromCell.Parent

commenting out the fromCell.Parent reference fixes this.