Hi,

Any help with this would be much appreciated.

I have a workbook that contains the formula below:
=IF(A2<>"",VLOOKUP(A2,'All Steps'!A:AH,34,FALSE),"")

The "All Steps" worksheet data needs to be updated regularly and is currently copied and pasted from another workbook. However, the amount of data contained in this worksheet is huge so to reduce the file size I'm using a macro to copy/paste special (values) the returned VLOOKUP results into another worksheet and then clearing the All Steps data.

The amount of data now contained with the All Steps worksheet is now so great that it is causing my PC to freeze for an age at a time whilst I copy / paste it from the original document into my workbook (even when pasting values only) and sometimes it's causing the application to crash depending on what other applications I have open at the time.

I noticed that if I moved the worksheet tab from the original document into my workbook it transfers significantly quicker but once I delete the sheet afterwards it causes all the VLOOKUP formulas to contain the #REF error rather than the worksheet lookup reference.

Is it possible to retain the worksheet reference using the INDIRECT function nested within the VLOOKUP and force excel to lookup the data when the worksheet is moved across and not if the worksheet is deleted; and if so, could someone show amend the function above as an example (I've no experience of using INDIRECT before)?

Alternatively, if I'm over complicating this and there is a quick way to copy the huge amount of data over (it's about 13Meg without formatting) Could someone explain me how to do this?

Thanks,
Matt