Copying sheet with relative formula named range causes errors.

Hello!

I use this copy sheet code and it works great. Occasionally, though, I get an error popping up: "A formula or sheet you want to move or copy contains the name 'ErrorCheck', which already exists on the destination sheet. Do you want to use this version of the name?"

If I click Yes, then the named range ErrorCheck is used, but it is the sheet ErrorCheck rather than the workbook-wide ErrorCheck.

Anyone have any ideas how to eliminate this error?

Please Login or Register  to view this content.

Named Ranges:

Please Login or Register  to view this content.

Thanks!

Lost

Jerry,

The error pops up sporadically, so hopefully you see it:

1) Go to one of the green sheet tabs.
2) Click the Copy Sheet button.
3) Name the copied sheet something that starts with a number ("0050", etc.) (I don't know if using a number as the sheet name causes the error, but that is what we name our sheets. Maybe something else will make it pop up.)
4) If all goes well, you'll see the error described above.
5) Otherwise, the sheet will copy just fine with no error.

Thanks for looking into this!

Lost

Jerry,

I tried to apply your code to my code, but I don't know what I am doing.

This isn't right:

Please Login or Register  to view this content.

Can you help?

Respectfully,

Lost

Jerry,

I updated the SHEETEXISTS function with what you supplied, but I don't know where in this code I need to put your code from Post #4.

Here is what I have, but it needs your #4 post code:

Please Login or Register  to view this content.

??

Lost

Jerry,

1. Go to the "PT (Master)" tab on the workbook attached to this post.
2. Click the Copy Sheet button.
3. Name that new sheet "a".
4. Now go the "a" sheet.
5. Click the Copy Sheet button.
6. Name that new sheet "b"
7. You should get the error.

HTH!

Lost

Jerry,

You made the copy from the copy, right?

PT (Master) makes "a".

"a" makes "b"

And you didn't get the error?

(This is running on Excel 2003.)


Lost

Jerry,

I tried it again with the file from post #10. When I make a copy of a copy, I get the error.

(If I go to the PT Master and copy to sheet "a", and then go back to PT Master and copy to sheet "b", I don't get the error.

If I go to PT Master and copy to sheet "a", and then go to sheet "a" (copy of a copy) and try to copy to sheet "b", I get the error.)

?

Lost