Sort Named Range with VBA

[petrovich17]

Hi All,

I am attempting to sort a named range with VBA. I have code that creates and sets the range, and code that sorts it. I have printed the range to the immediate window and the range is correct. However, when I try to sort using the named range, I get an error that says, "Run time error '1004': Method 'Range' of object '_Worksheet' failed." If I run the same code with the range manually typed in (ie. sht.Range("ottab") replaced with sht.Range("A1:V31") the code works correctly. Any help in troubleshooting this issue would be greatly appreciated.

Sub sortTest()
'
' sortTest Macro


Dim ottab As Range
Dim sht As Worksheet, bottomMostRow As Long, rightMostColumn As Long
Set sht = ActiveSheet
With sht
    bottomMostRow = .Cells(1, 1).End(xlDown).Row
    rightMostColumn = .Cells(1, 1).End(xlToRight).Column
    Set ottab = .Range(.Cells(1, 1), .Cells(bottomMostRow, rightMostColumn))
End With


Debug.Print ottab.Address(external:=False)


sht.Sort.SortFields.Clear
sht.Range("ottab").Sort Key1:=sht.Range("V1"), Order1:=xlAscending, _
Header:=xlYes

End Sub

[mdmackillop]

ottab is a range variable, not a range name

ottab.Sort Key1:=sht.Range("V1"), Order1:=xlAscending, _
    Header:=xlYes

[petrovich17]

Thanks very much. This is my first foray into writing VBA so I thought it would be something simple like that. Greatly appreciated.