sheet-copy turns INDEX to #N/A

[dlh]

When I copy a sheet of my workbook, all the User Defined Functions get replaced with "=#N/A" on the copied sheet.

There are easy workarounds and I don't expect to be duplicating worksheets very often, but I'd really like to understand why this is happening. A full recalculation seems to be triggered at the same time, so that's probably related.

I tried recreating the problem with a really simple UDF in an empty workbook and the problem doesn't occur. So it's caused by something in my project workbook. What should I be looking for?

[JBeaucaire]

Your UDFs are located in a standard module? Not the sheet module?

You're copying the sheets to another workbook or another sheet in the same workbook? If you copy to another workbook, you'll need to put the UDF into the new workbook, too.

[dlh]

Yes, the UDFs are in a standard module (coincidentally called UDFs).

And yes, the problem occurs when creating a duplicate sheet within the same workbook.

[DonkeyOte]

I think we need to see one of the offending UDF's such that we can see how everything is qualified etc...

Incidentally, were the UDFs stored inside a sheet object they would generate #NAME? rather than #VALUE! errors (on all sheets).

[dlh]

On further examination, the problem doesn't seem to be created by the UDFs at all, but rather their dependencies. New symptom: it also is happening with an INDEX function (dependent on a named range on another sheet).

I should emphasize, the copied functions are not returning #N/A. The cell formula actually contains =#N/A on the copied sheet (where it contained =INDEX(Sheet1!myrange,10,2) on the original sheet).

It seems related to recalculation. (My primary interest is in optimizing the recalculation time, and I hope understanding this will teach me something useful.)

[DonkeyOte]

Without seeing anything it will be hard for people to pass comment.

If I understand... you're now saying I think that the issue is not related to the "copy" of the sheet rather the sheet being copied contains error values in the first instance ?

You state that

=INDEX(Sheet1!myrange,10,2)

is generating an NA() error on the original ... so the obvious question would be: how is myrange defined ?

Assuming there's no issue with the named range in it's own right you would need to review the underlying value in row 10 column 2 of that range.

If you can, post a sample which demo's the problem.

[dlh]

Thank you, DonkeyOte, for your thoughts. I probably did not explain the example clearly enough.

In cell Sheet2!A1 I typed =INDEX(Sheet1!myrange,10,2) and it generates the expected result without any errors. I then copied the entire Sheet2, creating a new duplicate sheet. Then cell A1 on the new sheet contains =#N/A; the original formula did not get copied at all.

I'd like to understand why this could happen. Unfortunately it's not practical for me to post the whole workbook at present.

[DonkeyOte]

I'm afraid I have nothing to add at this point - we need to be able to replicate - as yet we still don't know how myrange is defined in the first instance.

Regards below:

I then copied the entire Sheet2, creating a new duplicate sheet. Then cell A1 on the new sheet contains =#N/A; the original formula did not get copied at all.

So how exactly are you "copying" the sheet ? Are you:

a) creating a new sheet and then going back and copying / pasting ?
(if so are you using values only ?)

or

b) right clicking on the Sheet2 Tab and selecting "Move or Copy", subsequently checking "Create a Copy" ?