VBA - Run time error '9': subscript out of range

[LPieter]

Hi everybody

I'm trying to write a macro to enable colleagues to sort a table on a protected worksheet. This table and worksheet are protected in regards to formatting to prevent unwanted changes to the tabel by colleagues who aren't very excel-savvy. Unfortunately, because of the locked formatting, it's impossible to sort the table columns as you wish without removing the protection.
To solve this problem, I'd like to add a clickable button above the table that sorts the table by running a macro. I think I got the macro, but keep on getting an error. I have no idea what the problem is and would love your advice.
Many thanks, Pieter

Sub sort_table()

Application.ScreenUpdating = False

activesheet.Unprotect Password:="Formules"

Dim ws As Worksheet
Dim iTable As ListObject
Dim wsname As String
Dim Tname As String

Set ws = activesheet
wsname = activesheet.Name
Tname = "T" & wsname

Set iTable = ws.ListObjects("Tname").Range

With iTable.Sort
.SortFields.Clear
.SortFields.Add Key:=Range("C3"), SortOn:=xlSortOnValues, Order:=xlAscending
.SortFields.Add Key:=Range("D3"), SortOn:=xlSortOnValues, Order:=xlAscending
.SortFields.Add Key:=Range("B3"), SortOn:=xlSortOnValues, Order:=xlAscending
.Header = xlYes
.Apply
End With

activesheet.Protect Password:="Formules", DrawingObjects:=False, Contents:=True, Scenarios:=False, AllowSorting:=True, AllowFiltering:=True

Application.ScreenUpdating = True

End Sub

 

[Kaper]

Probably you got this error in setting iTable. And probably you have in your worksheet an listobject (Excel Table) named after sheet name, but with added T at front.
If it is like that, then:
Try content (not name - in doble quotes) of Tname variable:

Set iTable = ws.ListObjects(Tname).Range

 

[LPieter]

Probably you got this error in setting iTable. And probably you have in your worksheet an listobject (Excel Table) named after sheet name, but with added T at front.
If it is like that, then:
Try content (not name - in doble quotes) of Tname variable:

Set iTable = ws.ListObjects(Tname).Range

Thank you for your reply. I already tried this as the debug interface highlights this line:

Set iTable = ws.ListObjects(Tname).Range

. The same error appears after running the macro without the quotes around Tname.

 

[RoryA]

That code should give you a type mismatch since iTable is a ListObject not a Range. It should be:

Set iTable = ws.ListObjects(Tname)

If that still gives a subscript out of range, then the table name is wrong. If it's the only table on the worksheet, you can use:

Set iTable = ws.ListObjects(1)