Data validation named range update

[Guest]

I delete a sheet containing named ranges with data in column A that are used
for data validation on another sheet in the workbook. I copy a sheet into
the workbook that contains the same names with revised ranges and values -
some with additional values - some additional named ranges. The lists no
longer show up in the validated cells on the main sheet. If I click in a
validated cell and check the defined name, it equates to =#REF!$A$1:$A$9.
The revised named range is actually larger.

Is there a way to programmatically update the references on the main sheet
to reflect the revisions on the data sheet?

 

[Guest]

Hi Phil-

The answer may lie in how you are adding the new sheet. If you are actually
using Copy & Paste, that could be the problem.

Have you tried opening both files and from the file with the new info page
active go to Edit>Move or Copy Sheet (or rt-click the sheet tab). Open the
list and choose the other book, specify where to put it and tick the checkbox
for 'Copy' (unless you actually want to Move the sheet to the other book).

When the sheet gets copied to the other book the defined ranges should be
there with it.

HTH :>)

 

[Guest]

I am doing it with the following code, rolling changes in the "template" into
all existing workbooks based upon it. Code snippet follows...
'=====================================================
' Check to see if the last occupied row on sheet "data" is the same as the
template
' If not, delete that sheet and replace it with the one from the template
'=====================================================
If (intDataRowMax <> intTempDataRowMax) Then
strModifiedFlag = True
ModelBook.Worksheets("data").Delete
TemplateBook.Worksheets("data").Copy ,ModelBook.Worksheets("Sheet2")
End If

 

[CyberTaz]

Sorry, Phil, but I'm not the VBA whiz. I can tell that your code appears to
be copying data from the source sheet as the normal copy & paste referred to
in my earlier post. What you are probably in need of is a docommand that
runs the Move or Copy Sheet command, but I'm at a loss as to what it might
be.

There are a number of knowledgeable people that monitor this forum, so you
should continue to check back. There is also the
microsoft.public.excel.programming group that you might want to check in
with.

Good Luck |:>)