REF to a worksheet which doesn't exist YET.

[esherm22]

I have a workbook with one worksheet. I have a macro that will copy in
worksheets from another workbook and then save the merged workbooks as
a new workbook.

I have two questions that I hope someone can help me with.

Issue 1.

I have some references on the initial worksheet that are referencing
the worksheets that will eventually be merged into a new workbook and
saved. Even after I open the newly merged document with all the
worksheets, it still has the #REF! in the cells and it opens a browse
dialog box to look for the document which has these worksheets. If I
click on a cell to see the formula and hit enter it populates with the
correct data.

Is there a way to make these #REF!'s populate automatically since the
references do exist in the new workbook?


Issue 2.

When I open the workbook with the one worksheet, It wants to find the
missing references. Like above the browse dialog box comes up. This
error I understand because the worksheets it is looking for aren't in
this workbook yet.

Would "application.displayalerts = false" fix this problem and how
could I change this so it sets it to false when the workbook is
opening.


I hope this makes sense.

Thanks & Regards,

Erik

 

[Dave Peterson]

#1. If you wrap =indirect() around the reference, you can trap an error before
excel wants to go looking for that worksheet:

For instance:

=IF(ISERROR(CELL("Address",INDIRECT("'notyetthere'!A1"))),"No Sheet",
VLOOKUP(A2,INDIRECT("'notyetthere'!A:Z"),18,FALSE))

(You can use any formula you want instead of the =vlookup().)

#2. I don't think you can stop xl's behavior -- except by doing something like
#1.