CODEWITHSUNDEEP
excelvba
Histerical
Histerical

Reputation: 304

Find Sheet Name with table on it

I am trying to find the sheet name that has a specific table name on it. For example:

Set sheetNM = ActiveWorkbook.Names("ratetable").RefersToRange.Parent.Name

Something like that, but would pull the name of the sheet, so I can activate that sheet in order to pull information from it.

Upvotes: 1

Views: 5983

Answers (4)

Gerhard
Gerhard

Reputation: 851

With the name of the table in A1, you can get the sheet name with this formula (directly in Excel, no VBA needed):

=MID(CELL("filename",INDIRECT(A1&"[#All]")),FIND("]",CELL("filename",INDIRECT(A1&"[#All]")))+1,999)
  • INDIRECT(A1&"[#All]") returns the table itself.
  • CELL("filename",INDIRECT(...)) returns the file name including the sheet name of the table, in a format like "C:...[filename.xlsx]SheetName".
  • FIND("]", CELL(...)) returns the index of the closing bracket in the file name with the sheet name.
  • MID(CELL(...),FIND("]", CELL(...))+1,999) extracts the sheet name from the file name, from after the closing bracket to the end.

Upvotes: 0

MacAbe
MacAbe

Reputation: 1

I know this post is old, but for what it's worth, I think the OP was on the right track (looking for the parent name) with the initial code that you originally posted. Calling the table's parent works for me:

ActiveSheet.ListObjects("TableName").Parent.Name

Upvotes: -1

John Coleman
John Coleman

Reputation: 52008

You can use error trapping to find the sheet containing a table with a given name:

Function FindTableSheet(TableName As String) As String
    Dim ws As Worksheet
    Dim LO As ListObject
    Dim shName As String

    For Each ws In Sheets
        On Error Resume Next
        Set LO = ws.ListObjects(TableName)
        If Err.Number = 0 Then
            FindTableSheet = ws.Name
            Exit Function
        Else
            Err.Clear
        End If
    Next ws
    FindTableSheet = "Not Found"
End Function

To test it, I named one of my sheets "Data" and added a table called "ratetable" to that sheet. I didn't, however, create any table called "table tennis". I then ran:

Sub test()
    Debug.Print FindTableSheet("ratetable")
    Debug.Print FindTableSheet("table tennis")
End Sub

With the output:

Data
Not Found

Upvotes: 1

user4039065
user4039065

Reputation:

This is not something I recommend but as you are referencing the ActiveWorkbook, you can drop the ActiveWorkbook and retrieve it simply as,

dim pws as worksheet, sws as string
sws = range("ratetable").parent.name
set pws = range("ratetable").parent
debug.print sws & " - " & pws.name

While a structured table (aka ListObject object) is listed in the Formulas ► Name Manager, it does not have all of the properties of a defined name. Unfortunately, everything you can do with a name you cannot always do with a ListObject as a ListObject's parent is the Worksheet object, not the workbook.

Upvotes: 2

Related Questions