Worksheet name appears in formulas?

[Maury Markowitz]

I don't know what triggers this, but periodically the name of a
worksheet suddenly appears in all of my formulas. The original formula
is...

=NPM!$P$4+NPM!$Q$1

NPM is the name of a sheet that is created on-the-fly by some VBA code
in the workbook. This works fine for a while, but eventually we will
open the workbook and find that all of these have been replaced by:

=[NPM]NPM!$P$4+[NPM]NPM!$Q$1

Now whenever the workbook recalcs, is puts up an Open dialog and asks
us to Update Values. There are dozens of examples of this formula
around the workbook, so it's really annoying. Adding to the
frustration is that when I remove them from the formulas by had it
adds them back in!

Anyone know what's going on here?

Maury

 

[Joel]

A name in square brackets indicates a workbook name. becuase it doesn't have
an ".xls" indicates it hasn't been saved. It look like the macro creates a
temporary workbook while it is running.

One possibility is the code copies a worksheet and doesn't use a "before" or
"after" as part of the copy instruction. When "before" or "after" is not
used a new workbook is created.

To confirm if a new workbook was created look at the VPA project window
after the macro is run and see if there is a new workbook.

 

[Maury Markowitz]

The new worksheet is definitely created. And if you go and fix the
formulas after it has been created, it works fine. But I have to do
this every night!

Maury