make a list of the listobjects on a worksheet
 

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

make a list of the listobjects on a worksheet
 

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

--
HTH,
Barb Reinhardt



" wrote:

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

make a list of the listobjects on a worksheet
 

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

On Aug 27, 1:28 pm, Barb Reinhardt
wrote:
This should get you started

Sub test()
Dim LObs As Long
Dim WS AsWorksheet
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 & "listobjects in the workbook."
End Sub

--
HTH,
Barb Reinhardt

" wrote:
Hi,

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

How can I generate the names of thelistobjects on aworksheet?

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 & "listobjects in the workbook."
End Sub

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

Thanks