vlookup and return entire cell including formatting from non active workbook

Issue

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?

Solution

Set extractDestRange = Range(destAddr) ‘fromCell.Parent

commenting out the fromCell.Parent reference fixes this.

Answered By – Nik Burns

Answer Checked By – Robin (AngularFixing Admin)

Leave a Reply

Your email address will not be published.