Moving a sheet from one book to another Ref# error

[royhern]

Hey team, i have two workbooks and both have different names. In both workbooks their is a tab named "DATA". I want to be able to delete the "Data" on the destination workbook and copy the other into this workbook. I tried moving/copy the data tab from one workbook into the other and i got an REf# error.

This is the formula on the workbook before i copy the data tab:

=IF(ISERROR((IF(ISBLANK(VLOOKUP('Cover pg 1'!I42, DATA!1:65536,3,FALSE)),"",(VLOOKUP('Cover pg 1'!I42, DATA!1:65536,3,FALSE))))),"",(IF(ISBLANK(VLOOKUP('Cover pg 1'!I42, DATA!1:65536,3,FALSE)),"",(VLOOKUP('Cover pg 1'!I42, DATA!1:65536,3,FALSE)))))

This is the formula after i copied the data tab:

=IF(ISERROR((IF(ISBLANK(VLOOKUP('Cover pg 1'!I42, #REF!1:65536,3,FALSE)),"",(VLOOKUP('Cover pg 1'!I42, #REF!1:65536,3,FALSE))))),"",(IF(ISBLANK(VLOOKUP('Cover pg 1'!I42, #REF!1:65536,3,FALSE)),"",(VLOOKUP('Cover pg 1'!I42, #REF!1:65536,3,FALSE)))))


Is there a way to move/copy the data tab without causing all types of errors (including macros).

Thanks, Roy

 

[walt1122]

I just had a similar need. I have a bunch of cells linked to a tab called 508 on a the worksheet. If I try to replace the data in the 508 with new stuff it gives me the REF error. So I figured out I would have to trick it. The formulas know if you chande the name of a tab so I pull in the 508 with the new data and excel knowing that the tab name is in use calles the new one 508 (2). I have the Find/Replace change the links by FIND 508 and REPLACING it with 508 (2). Now I delete the old 508 tab and rename the new 508 (2) 508. The macro below describes this action. See if it will work for you

Walt

Workbooks.Open FileName:= _
"\MTOFS003sharedcommonchrbenefitssharedHYPRPREP