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

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" <(E-Mail Removed)>
wrote in message
news:33e876b5-fce1-45a1-a377-(E-Mail Removed)...
> 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
>
>

Hej Jim.

Thank you.

Med venlig hilsen
Peter

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.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc