Display Worksheet name and Refers To Range for tables

Try this:

Function Table_Range(TableName As String) As String
Dim sh As Worksheet             ' Pointer to sheets in the workbook
Dim lo As ListObject            ' Pointer to list objects on the sheet
Dim tbl As ListObject           ' Pointer to the table
Dim SheetName As String         ' Sheet name on which the table was found

' Loop though each sheet
For Each sh In ActiveWorkbook.Sheets
    ' Loop through list objects on the sheet
    For Each lo In sh.ListObjects
        ' If the list object name = the table name return sheet name and table address
        If lo.Name = TableName Then
            SheetName = sh.Name
            Set tbl = sh.ListObjects(TableName)
            Table_Range = "'" & SheetName & "'!" & tbl.Range.Address
            Exit Function
        End If
    Next lo
Next sh

Table_Range = "Table not found"

End Function