define name

[Guest]

Selection.CurrentRegion.Select
Selection.CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False

The above code define the names of the top colums in my selection - but it
does that for the wotksheet level - I will need to have that define locally
for the sheet only.

Any idea?

M

 

[Dave Peterson]

It doesn't look like there's an option that allows you to make the names local.

But you could do it with something like:

Option Explicit
Sub testme01()

Dim myRng As Range
Dim myCol As Range

Set myRng = Selection.CurrentRegion

For Each myCol In myRng.Columns
With myCol
.Resize(.Rows.Count - 1, 1).Offset(1, 0).Name _
= "'" & .Parent.Name & "'!" & .Cells(1).Value
End With
Next myCol

End Sub

 

[Guest]

Thank you

Option Explicit
Sub testme01()

Dim myRng As Range
Dim myCol As Range

Set myRng = Selection.CurrentRegion

For Each myCol In myRng.Columns
With myCol
.Resize(.Rows.Count - 1, 1).Offset(1, 0).Name _
= "'" & .Parent.Name & "'!" & .Cells(1).Value
End With
Next myCol

End Sub