Name Cells???

[buzz71023]

Does anyone know if it is possible to have two cells/Ranges named the same thing in one Workbook (Two different Sheets)? If so how might I accomplish that.

I want Cell "D1" on Sheet1 to be named "Test"
and Cell "T17" on Sheet2 to be named "Test" as well.

this doesn't have to be done in VBA, I will just help reduce several of my codes if I can get them named the same thing.

 

[wigi]

Hello
That's possible. When creating the named range (for instance, Ctrl-F3), indicate that you want a sheet level named range. (As opposed to the default Workbook-level named range).

Hence, you choose a name on a smaller scope.

 

[Smitty]

In the Define Name-->New dialog there's a Scope selection where you can limit a named range to a specific worksheet.

HTH,

 

[buzz71023]

Never mind I think I figured it out... If there is a way to do in in VBA I wouldnt mind knowing that for future ref.

 

[Smitty]

If there is a way to do in in VBA I wouldnt mind knowing that for future ref.

Just record a macro. You'll see the difference in the recorded code between setting a named range with workbook scope vs. worksheet.

 

[wigi]

Never mind I think I figured it out... If there is a way to do in in VBA I wouldnt mind knowing that for future ref.

For named ranges with a global (workbook) scope, the VBA command is:

ActiveWorkbook.Names.Add Name:="...", RefersToR1C1:="..."

For named ranges with a local (worksheet) scope, the VBA command is similar but you add the worksheet name:

ActiveWorkbook.Worksheets("Sheet1").Names.Add Name:="...", RefersToR1C1:="..."

Of course, ActiveWorkbook or worksheet references could be different. As long as you have valid workbook and/or worksheet references, you can add a member to the resp. Names collection.

 

[buzz71023]

Thanks for the help

 

[buzz71023]

yeah I was going to just record the macro but the codes are usually much longer than someone with experience.