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