What I am doing is copying a sheet from a different workbook to my current workbook. I'm basically doing the following:
Delete the current Worksheet in the current Workbook
Open the external Workbook and Copy the Worksheet required
This all works as expected but all references in the other sheets are lost and replaced with #REF.
Is there a workaround (other than find and replace hack) that can be done to avoid this.
Regards,
Lloyd
You could try using Clear and Copy-Paste instead:
- Clear the contents of the current worksheet
- copy the external worksheet
- paste into the current sheet
Why don't you just copy the values contained in the sheet instead of the whole sheet object?
v = Workbooks("Book1").Worksheets("Sheet2").Range("A1:IV65536")
Workbooks("Book3").Worksheets("Sheet1").Range("A1:IV65536") = v
where v is a Variant. Or,
Workbooks("Book3").Worksheets("Sheet1").Range("A1:IV65536") = _
Workbooks("Book1").Worksheets("Sheet2").Range("A1:IV65536")
This takes a couple of seconds, but will be faster if you reduce the range to what you really need ("A1:IV65536" is presumably exaggerated...)
Of course this won't do if you also have formulas in the sheet you're copying and you need those formulas in the destination sheet. It isn't clear from your question what exactly you're trying to accomplish.
