OK, Had some more time to play, waiting for
@snets123 to respond so I came up with the following code to gather the Table names from all of the worksheets, as well as the worksheet that the table is on, if that info would be desired. I also incorporated arrays, as suggested, to gather that information.
VBA Code:
Sub ListAllWorksheetsAndTableNames() ' List all Worksheets/Table Names in the A/B columns of a new sheet
'
Dim ListOfTableNamesSheetExists As Boolean
Dim TableName As ListObject
Dim RowCounter As Long
Dim SheetCounter As Long
Dim TableCounter As Long
Dim ColumnA_ArrayList As Object
Dim ColumnB_ArrayList As Object
Dim ws As Worksheet
'
Set ColumnA_ArrayList = CreateObject("System.Collections.ArrayList") ' Set up ArrayList for Column A
ColumnA_ArrayList.Add "Worksheet Name" ' Add a Header to fill Item(0)
'
Set ColumnB_ArrayList = CreateObject("System.Collections.ArrayList") ' Set up ArrayList for Column B
ColumnB_ArrayList.Add "Table Name" ' Add a Header to fill Item(0)
'
RowCounter = 0 ' Initiate RowCounter
'
For Each ws In Worksheets ' Go through each worksheet in the worksheets object collection
If ws.Name = "ListOfTableNames" Then ListOfTableNamesSheetExists = True ' If sheet name found then set flag to True
Next
'
If Not ListOfTableNamesSheetExists Then Sheets.Add.Name = "ListOfTableNames" ' If aheet name not found then add it to the workbook
'
For Each ws In Worksheets ' Go through each worksheet in the worksheets object collection
For Each TableName In ws.ListObjects ' Go through all table names located in the current worksheet
RowCounter = RowCounter + 1 ' Increment RowCounter
'
ColumnA_ArrayList.Add ws.Name ' Save Worksheet name containing the Table to cell in column A
ColumnB_ArrayList.Add TableName.Name ' Save Table name to cell in column B
Next
Next
'
' Write results to columns A/B of Sheets("ListOfTableNames")
Sheets("ListOfTableNames").Range("A1").Resize(ColumnA_ArrayList.Count, 1).Value = Application.Transpose(ColumnA_ArrayList.toArray)
Sheets("ListOfTableNames").Range("B1").Resize(ColumnB_ArrayList.Count, 1).Value = Application.Transpose(ColumnB_ArrayList.toArray)
'
End Sub
