How to avoid questions during worksheets copy before ?

[Peter]

In my Excel 2010 workbook I want to copy a "master-sheet" e.g. a
formatet sheet with named fields etc but without data.

I use the Worksheets("master").Copy Before:=Sheets(2)

The challenge is, that the user is asked if he want to use the
rangenames from the master-sheet again. Can I avoid that (those, one
for each named range) questions. I have tried with On Error Resume
Next, buy as the question is not an error that statement has no
effect.

Regards

Peter

 

[Jim Cone]

Use...

Application.Display.Alerts = False

....This must be set to true before exiting your code.
(note that there is more than one way to exit code)
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(free and commercial excel programs)





"Peter" <[email protected]>
wrote in message
news:33e876b5-fce1-45a1-a377-6417ae486baf@eb1g2000vbb.googlegroups.com...

 

[Peter]

Hej Jim.

Thank you.

Med venlig hilsen
Peter

 

[GS]

Peter expressed precisely :

In my Excel 2010 workbook I want to copy a "master-sheet" e.g. a
formatet sheet with named fields etc but without data.

I use the Worksheets("master").Copy Before:=Sheets(2)

The challenge is, that the user is asked if he want to use the
rangenames from the master-sheet again. Can I avoid that (those, one
for each named range) questions. I have tried with On Error Resume
Next, buy as the question is not an error that statement has no
effect.

Regards

Peter

The question is being asked because the range names used on
Sheets("master") are NOT sheet-level names and so belong to the
workbook, NOT Sheets("master"). Workbook-level names are shared and so
formulas using those names will always refer to the sheet they were
defined on if you answer YES to the question raised when copying the
master sheet.

If you use Sheets("master") as a template, you should give the named
ranges sheet-level scope in the 'Name Manager' dialog. This keeps the
named ranges refs confined to each copy of the sheet and so the
question will never be asked, and formulas using those names will ref
the sheet they're used on.