copy worksheets between workbooks - including named ranges

[christian_spaceman]

Hi,

I'm working on a workbook which makes extensive use of named ranges
(which is good). However, depending on what a user selects, one of the
worksheets may need to be replaced with another (the user can select
whether to run a "full" or "mini" run of the model, if a mini is run
then one sheet needs to be replaced).

Because the rest of the sheets in the workbook reply on named ranges
in the "full" sheet, when it is replaced with the "mini", I need the
same named ranges to be there.

I am trying to replace the sheet as follows:


Set sourceworkbk = Workbooks("Valuation_mini.xls")
Set destworkbk = Workbooks("Valuation_full.xls")
' copy the worksheet over
sourceworkbk.Worksheets("mini calculation").Copy
after:=destworkbk.Worksheets(destworkbk.Worksheets.count)

this copies the worksheet into the workbook fine, but does not appear
to bring the named ranges with it. I.e. named ranges defined in
Valuation_mini.xls are not available once the sheet is copied into
Valuation_full.xls.

Is there any way to force it to bring named ranges with it?

Thanks in advance,

Chris

 

[Bob Phillips]

Isn't that its default behaviour?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[christian_spaceman]

It doesn't appear to be. A named range available in the sheet before
the copy isn't in the destination book after the copy.