Code runs in Module not in sheet code

[mahhdy]

Hi,
I have a custom sort code, which runs perfectly when I am calling it from a module, but when I copy it inside the my codes in the sheet, and address it, it returns error.
Can somebody help and explain?

here is my code:

Sub custom_sort()


Application.calculation = xlManual
Application.EnableEvents = False
Application.ScreenUpdating = False
Call identification


Dim Customlist(250) As String
Dim ii As Double, c As Range
ii = 0
For Each c In Selection
    If Not c.Rows.Hidden Then
        Customlist(ii) = c
        Debug.Print "ii = "; ii & " " & Customlist(ii)
        ii = ii + 1
    Else
    End If
Next
Application.AddCustomList ListArray:=Customlist
    tb.Sort.SortFields.Clear
    tb.Sort.SortFields.Add Key _
        :=Range("tbl[Work Book]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        CustomOrder:=Application.CustomListCount, DataOption:=xlSortNormal
    ws.Activate
    With tb.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Application.DeleteCustomList Application.CustomListCount
Application.calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub

 

[RoryA]

What is the error and on which line? The first thing that comes to mind is that if the tbl Table is not on the sheet you put the code in, you need to use:

Application.Range("tbl[Work Book]")

in the sort key, and not just:

Range("tbl[Work Book]")

It's not clear what tb is in your code, so I can't say if that might also be an issue.

 

[mahhdy]

Great Help..
Thanks it fixed by adding application.range

the error was on the same line.
Yours,
M

What is the error and on which line? The first thing that comes to mind is that if the tbl Table is not on the sheet you put the code in, you need to use:

Application.Range("tbl[Work Book]")

in the sort key, and not just:

Range("tbl[Work Book]")

It's not clear what tb is in your code, so I can't say if that might also be an issue.