Moving a sheet from one book to another Ref# error

royhern

Board Regular
Joined
Feb 21, 2002
Messages
158
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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

walt1122

Active Member
Joined
Jun 6, 2002
Messages
318
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
 

Forum statistics

Threads
1,144,446
Messages
5,724,400
Members
422,550
Latest member
Parvin_B

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top