make a list of the listobjects on a worksheet

[gordonjster]

Hi,

I am sure this must be easy, but somehow I'm not "getting it" today.

How can I generate the names of the list objects on a worksheet?

This is close, except it gives me the count:
Sub test()
Dim i
Dim LObs
For i = 1 To Worksheets.Count
LObs = LObs + Worksheets(i).ListObjects.Count
Next i
MsgBox "There are " & LObs & " list objects in the workbook."
End Sub

I'd like to know the item names of the listobjects.

Thanks

 

[Barb Reinhardt]

This should get you started

Sub test()
Dim LObs As Long
Dim WS As Worksheet
Dim aWB As Workbook
Dim ListObj As ListObject

Set aWB = ActiveWorkbook
For Each WS In aWB.Worksheets
LObs = LObs + WS.ListObjects.Count
For Each ListObj In WS.ListObjects
Debug.Print WS.Name, ListObj.Name
Next ListObj
Next WS
MsgBox "There are " & LObs & " list objects in the workbook."
End Sub

 

[gordonjster]

Hi Barb,

Yes, thank you very much!

On my own I came up with something similar

Sub GetNames()
Dim obj as ListObject
For Each obj In ActiveSheet.ListObjects
MsgBox obj.Name
Next obj
End Sub

Life is good again.

-Jim