Retrieve Names of Smart Tables And Corresponding Worksheets

Dear Experts:

My current workbook is a multi-worksheet workbook.

All of these worksheets (with the exception of a worksheet named 'Overview) contain smart tables which I have named such aus 'MT_Surgery' or ' CT_Scissors' etc.

I now would like to retrieve all the names of these smart tables along with their location, i.e. which worksheet these named smart tables reside in.

This data is to be compiled in the worksheet named Overview.

So the result should look like this in the worksheet called 'Overview'


I guess this can only be achieved using a macro.

Help is very much appreciated. Thank you very much in advance.

Regards, Andreas
Andreas HermleTeam leaderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
How is your question different from your last question?
Andreas HermleTeam leaderAuthor Commented:
It differs as follows:

My last question was based on the assumption that all the named smart tables have been filled in manually in Column B of the 'Overview' Worksheet.

The new question asks the experts to also retrieve the user-defined names of the smart tables.

Thank you .
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Okay. Please give this a try and let me know if this is what you are trying to achieve.

Sub ListTableNames()
Dim dws As Worksheet, ws As Worksheet
Dim tbl As ListObject
Dim lr As Long

Application.ScreenUpdating = True
Set dws = Worksheets("Overview")
With dws.Range("A1:B1")
    .Value = Array("Name of Worksheet", "Name_Smart_Table")
    .Font.Bold = True
    .Font.Size = 13
End With
For Each ws In ThisWorkbook.Worksheets
    For Each tbl In ws.ListObjects
        If tbl.Name Like "??_*" Then
            lr = dws.Cells(Rows.Count, 1).End(xlUp).Row + 1
            dws.Range("A" & lr).Value = ws.Name
            dws.Range("B" & lr).Value = tbl.Name
        End If
    Next tbl
Next ws
dws.Range("A1").CurrentRegion.Borders.Color = vbBlack
Application.ScreenUpdating = True
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Andreas HermleTeam leaderAuthor Commented:
Great coding Subodh, I really appreciate your expertise :-) Thank you very much.

That is exactly how I wanted it.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Andreas!
Thanks for the feedback. :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.