Using Dynamic Ranges (without range address) inside a Formula

[Jan Karel Pieterse]

This code will create locally defined range names for each sheet which has a name starting with "DataSheet" (adjust accordingly):

Sub AssignRangeNames()
    Dim oSh As Worksheet
    For Each oSh In Worksheets
        If Osh.Name Like "DataSheet*" Then
            oSh.Range("A1").CurrentRegion.Name = "'" & oSh.Name & "'!LocalTable"
        End If
    Next
End Sub

[predzer]

This code will create locally defined range names for each sheet which has a name starting with "DataSheet" (adjust accordingly):

Sub AssignRangeNames()
    Dim oSh As Worksheet
    For Each oSh In Worksheets
        If Osh.Name Like "DataSheet*" Then
            oSh.Range("A1").CurrentRegion.Name = "'" & oSh.Name & "'!LocalTable"
        End If
    Next
End Sub

Thanks Jan Karel Pieterse. But as i mentioned in my first post,

"I need help to Replace cell range ($A$1:$I$1,K$1,$A2:$I2) to 'something' as i wont be knowing cell range except starting cell A1"

​(P.S. I can use your code in the later stage once i get solution to my first post. Thank You)