Create Local Range Names in VBA

[QB]

Hi

I need to create local range names in several sheets via VBA. The name for all the named cells will be "TabName" The values stored in the named cells will be used to build a lookup formulae using the INDIRECT function.

Any help would be much appreciated.

Regards

QB

 

[shg]

One way:

Sub x()
    Dim wks         As Worksheet
 
    For Each wks In Worksheets
        wks.Names.Add Name:="TabName", RefersTo:=wks.Range("A1:A10")
    Next wks
End Sub

 

[QB]

Hi shg

Many thanks for your reply. Your code didn't seem to work for me - it kept producing Global rather than Local named ranges but it did give me a strong steer to a solution. I eventually solved it with the following code:

With Cells(3, 1)
.Name = ActiveSheet.Name & "!" & "TabName"
.Value = sCoAbr
End With

Again, many thanks

QB

 

[RoryA]

FWIW, shg's code worked perfectly for me. You say it kept producing global names, but you can only have one global range with a given name, so how could that be?

 

[QB]

Hi Rorya

When I said it kept producing Global Names I should have said that one global name was created and it pointed to a range on the last worksheet in my collection of sheets when the code completed.

Looking more carefully at my attempt to use shg's code, I missed the wks. from wks.Range("A1:A10") when I modified shg's code. Putting that back in worked - local names as expected.

I have suitably chastised mysely - who am I to doubt a Mr Excel MVP!

Regards

QB

 

[RoryA]

He's also a Microsoft MVP...