How to find worksheet that contains specific table

[radek]

Hello everyone. I am in need to loop through all worksheets in my workbook and find which worksheet contain specific table. I know the table name, for instance table1. I thought that my concept will work, but it does not. I tried to use function intersect. I was thinking that if worksheet.cells intersects with table1 range then I finished my search.

If not intersect (sheets("aa").cells, sheets("aa").listobjects(table1)) is not nothing then ..
I have 20 worksheets in my workbook, so each time I am checking worksheet (in the loop) where there is not table1, then I am receiving error: Subscript out of range. And it make sense, there is no table1 on sheet("aa"), but may there is on sheet("bb"). Is there a better way to loop through worksheets to find that one that contains specific table, or maybe entirely different approach is needed in such search? Please help. thanks R

 

[DanteAmor]

How about:

Sub locateTable()
  MsgBox Range("Table1").Parent.Name
End Sub

The message shows you the name of the sheet where Table1 is located

 

[radek]

How about:

Sub locateTable()
  MsgBox Range("Table1").Parent.Name
End Sub

The message shows you the name of the sheet where Table1 is located

Dear Dante, it works. I can not believe haw simple it is. Thanks a lot.