Create the same named Range across multiple sheets

[Faridwahidi]

Hi All Expert,

I have been given a task to update data validation across multiple cells & worksheets. It working fine but I am really surprised why the named range only been created at the last worksheets. Based on the code, the named range "ScoreDecimal" supposed to be created in each sheets.

It the user remove the last worksheets, the updated data will gone

Please Login or Register  to view this content.

[Trebor76]

I am really surprised why the named range only been created at the last worksheets

Named ranges must be unique. You just have one so the code just keeps overwriting it till it gets to the last tab.

[Faridwahidi]

Hi Trebor,

Thanks for the reply.

However, if user manually create copy and renamed worksheets. The named ranges will appear in each sheets. please see attchment

named ranges.jpg

[Trebor76]

However, if user manually create copy and renamed worksheets. The named ranges will appear in each sheets

But if you manually create a data validation drop-down based on the MONTH named range it will always refer to the first MONTH named range as this is what Excel determines to be unique named range in this situation.

[Faridwahidi]

Thanks for the explanation. however, in my workbook, it seems not like that

if you create copy new worksheets and renamed it. Then remove the main worksheets, the new crated worksheets with the same named ranges is still working fine.

[nilem]

Hi Farid,
try this

Please Login or Register  to view this content.

[Faridwahidi]

Hi Nilem,

Amazing.. It works,

You should be promoted to Guru level