Range naming problems

[Sajit]

This post is a continuation from a related post under Access data base reports,

titled: Using OLE frame with an Excel sheet for a report template

After copying a sheet by copy sheet to a new sheet name. Excel also copys
the range name defined on that sheet. It creates a mutiple instance of the
same range name on different sheets. This duplicate is not always visible in
one does insert > name > define. It does showed up in one instance when I
closed and opened it again.

I have corrected the range name corresponding to the sheets. But the Access
report still picks the wrong template.

Does any one have a solution for this? please.

Sajit Viswan
Abu Dhabi

 

[Jan Karel Pieterse]

Hi Sajit,

After copying a sheet by copy sheet to a new sheet name. Excel also copys
the range name defined on that sheet. It creates a mutiple instance of the
same range name on different sheets. This duplicate is not always visible in
one does insert > name > define. It does showed up in one instance when I
closed and opened it again.

I have corrected the range name corresponding to the sheets. But the Access
report still picks the wrong template.

Copying a sheet to which a range name refers causes Excel to create a copy of
that range name local to the new worksheet.

So if you copied Sheet1, which had a range name "RangeName" referring to e.g.
cell $A$1:$D$100, you will now have a rangename called "'Sheet1 (1)'!RangeName"
(or similar, depending on the new worksheet's name), which refers to
'Sheet1 (1)'!$A$1:$D$100

When you're on Sheet1 (1), Insert, name define will only show the LOCAL name
"'Sheet1 (1)'!RangeName". When you're on any other sheet, you'll see the
original, global, name.

Get hold of my (and Charles Williams) Name Manager:

www.jkp-ads.com/officemarketplacenm-en.asp

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Sajit]

Jan,

Thanks for the information and the Manager utility.

I wonder if you would have any clue to my other problem where I am using the
range name in Access. It will be of great help if you can point me in a
direction of help.

http://www.microsoft.com/office/com...n-us&dg=microsoft.public.access.reports&fltr=

 

[Jan Karel Pieterse]

Hi Sajit,

I wonder if you would have any clue to my other problem where I am using the
range name in Access. It will be of great help if you can point me in a
direction of help.

Sorry, my Access knowledge is too limited to solve that matter.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com