Global Named Ranges Problem??

[zmhnk]

Hi

I have timesheet spreadsheet application,and this spreadsheet require to add new sheets for new employee using vba

iam adding new sheets by copying sheets("sample") for every new employee,and this sheet("sample") contains Named ranges with scope to workbook,but the problem is :
every new sheet added by copying sheets("sample"),make thier own range names

what i want is to make one global name range valid for all sheets without changing its scope to the new sheet added???
how??

 

[Jerry Sullivan]

Hi zmhnk,

Excel doesn't allow you to use the same Defined Name with Workbook scope to refer to ranges on different sheets.

You could either use the same Name on each sheet with Worksheet scope, or have your VBA code create unique names with Workbook scope for the different sheets.

Why do you want the names to have global (Workbook) scope?
You should be able to accomplish the same tasks with slightly different syntax using names with Worksheet scope.

 

[zmhnk]

no ,i use them to make the maintennce easy issue such in update version issues,so i want to use
global named range to make the changing very easy such modification the name range and so on

what about using vba code such like

thisworkbook.names .add  name:="rangeName"  ...etc

 

[Jerry Sullivan]

The problem is that you can't use the same name on each sheet and have each of those name references have Workbook scope.

For example, if Cell J2 on every employee's time sheet holds the total hours worked for the week, you might take one of these approaches:

1. Use Worksheet Scoped names: Each sheet would have the name "Hours" that refers to Cell J2 on that sheet.
If your sheets are named: Employee1, Employee2, Employee3.. then each of those sheets would have the name "Hours" that has worksheet scope.

On sheet Employee1, you can use the name in a formula just as you would a name that has Workbook Scope.
In Cell K2, you could have the formula =Hours/5 and that formula will refer to the cell Employee1!J2.

To reference that Cell from a different worksheet, you will need to add the sheet name, such as:
='Employee1'!Hours+'Employee2'!Hours

2. Use Unique Workbook Scoped names: J2 in each sheet could have a Workbook Scoped name that would be unique. For example, you could use the naming scheme Hours1 refers to Employee1!J2, Hours2 refers to Employee2!J2. This allows you to reference the names anywhere in the workbook without adding the Sheet reference such as:
=Hours1+Hours2

...however it makes things more complex because you can't use the same formulas on every sheet to refer to J2 on that sheet.

Hopefully this helps. Please clarify and provide an example if I misunderstood your question.