Create a list of all tables in a workbook

[Waimea]

Hi,

I am trying to create a list of all tables in a workbook and I am also trying to create an userform with one or more list boxes with the table names in them.

The listbox is called listbox1, listbox2 and listbox3.

Sub tableAllSheet()

    Dim sh As Worksheet
    Dim tbl As ListObject


    'Loop through all sheets
    For Each sh In ThisWorkbook.Worksheets
        'Loop through all table on a sheet

        For Each tbl In sh.ListObjects
            'Print table name, table header row address and data range address to Immediate window
            Debug.Print tbl.Name & vbTab & tbl.HeaderRowRange.Address & vbTab & tbl.DataBodyRange.Address
        Next tbl
    Next sh


End Sub

This code debug prints the information I am looking for and I want to print it to a worksheet?

 

[My Aswer Is This]

Re: How to create a list of all tables in a workbook

Thank you for your reply, it works great.

I am trying to create a table manager that in one listbox, listbox1, shows all table names, if I click on the table name I Application.Goto that table.

I another listbox, listbox2, I would like to show tables where I haven't run a specific macro. The macro creates a summary of the table.

In a third listbox I would like to show tables where the summary macro has been run.

Perhaps using a flag (TRUE or FALSE) to keep track on which tables the macro has been used?

I now see this posting. So for the other request about when scripts were run.

I would think I need to see the script you plan to run.

 

[Waimea]

Re: How to create a list of all tables in a workbook

I can't get the Goto Table to work. It says runtime error 1004.

 

[Waimea]

Re: How to create a list of all tables in a workbook

This is not my code but I am using it to create a summary of a table, it takes a table in report sheet and creates a new table in summary sheet.

 Sub test()    Application.ScreenUpdating = False
    Break_Down
    Summary
    Application.ScreenUpdating = True
End Sub


Private Sub Break_Down()
    Dim a, e, i As Long, myCountry As String, LastR As Range, dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    With Sheets("report")
        With .ListObjects(1)
            Set LastR = .Range.Offset(, .ListColumns.Count + 4).Cells(1)
            .AutoFilter.ShowAllData
            a = .DataBodyRange.Value
            For i = 1 To UBound(a, 1)
                If Not a(i, 1) Like "*(*)" Then
                    myCountry = a(i, UBound(a, 2))
                    If Not dic.exists(myCountry) Then
                        Set dic(myCountry) = Union(.HeaderRowRange, .DataBodyRange.Rows(i))
                    Else
                        Set dic(myCountry) = Union(dic(myCountry), .DataBodyRange.Rows(i))
                    End If
                End If
            Next
        End With
        LastR.CurrentRegion.EntireColumn.Clear
        For Each e In dic
            If Not IsEmpty(LastR) Then Set LastR = Cells(Rows.Count, LastR.Column).End(xlUp)(4)
            dic(e).Copy LastR
            With LastR.CurrentRegion.Offset(1)
                .Rows(.Rows.Count).Range("a1").Value = e & " (average)"
                .Rows(.Rows.Count).Range("b1").Resize(, .Columns.Count - 2).FormulaR1C1 = _
                "=average(r" & .Row & "c:r[-1]c)"
                .Rows(.Rows.Count).Cells(1, .Columns.Count).Value = e
            End With
        Next
        LastR.EntireColumn.AutoFit
    End With
End Sub


Private Sub Summary()
    Dim a, i As Long, ii As Long, myCountry As String, dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    a = Sheets("report").ListObjects(1).DataBodyRange.Value
    For i = 1 To UBound(a, 1)
        If Not a(i, 1) Like "*(*)" Then
            myCountry = a(i, UBound(a, 2))
            If Not dic.exists(myCountry) Then
                dic(myCountry) = Array(dic.Count + 1, 1)
                a(dic.Count, 1) = myCountry & " (average)"
                a(dic.Count, UBound(a, 2)) = myCountry
                For ii = 2 To UBound(a, 2) - 1
                    a(dic.Count, ii) = a(i, ii)
                Next
            Else
                For ii = 2 To UBound(a, 2) - 1
                    a(dic(myCountry)(0), ii) = a(dic(myCountry)(0), ii) + a(i, ii)
                Next
                dic(myCountry) = Array(dic(myCountry)(0), dic(myCountry)(1) + 1)
            End If
        End If
    Next
    For i = 0 To dic.Count - 1
        For ii = 2 To UBound(a, 2) - 1
            a(i + 1, ii) = a(i + 1, ii) / dic.items()(i)(1)
        Next
    Next
    With Sheets("summary")
        .Cells.Clear
        Sheets("report").Rows(9).CurrentRegion.Rows(1).Copy .Cells(1)
        .Range("a2").Resize(dic.Count, UBound(a, 2)).Value = a
        .Columns.AutoFit
    End With
End Sub

 

[My Aswer Is This]

Re: How to create a list of all tables in a workbook

I can't get the Goto Table to work. It says runtime error 1004.

It works for me.

You have your Table names loaded into the listbox correct?
That part you said worked I believe.

Now with this last script put into your Userform and assuming this listbox is named Listbox1

When you click on Dad in the listbox it should take you to a Table named Dad.

I test all my scripts.

 

[Waimea]

Re: How to create a list of all tables in a workbook

Hi My Aswer Is This,

you are correct and the table names are loaded into all 3 listboxes.

When I click on any table in listbox1 I get a error message at this line

Application.Goto Sheets(sws).range(ans)

.

 

[My Aswer Is This]

Re: How to create a list of all tables in a workbook

I suggest you delete the other two listbox's for now

Only have one listbox loaded with Table names.
And see what happens

But now you must also change your initialize code to only load one listbox with table names.

 

[Waimea]

Re: How to create a list of all tables in a workbook

Hi, I have made the changes you suggested and Application.Goto works if the table is in a visible sheet but not if the table is in a hidden worksheet?

That would explain why it doesn't work for me at the moment.

 

[My Aswer Is This]

Re: How to create a list of all tables in a workbook

That's true.
If you remember form last week I said you cannot Goto a sheet that is hidden.
So we can tell the script to unhide the sheet and then Goto the sheet or Give you a warning that the sheet is hidden like we did last week on the Goto in listbox1 from last weeks work.

So which do you want a warning or unhide then Goto.

 

[Waimea]

Re: How to create a list of all tables in a workbook

Hi, I would like to unhide and then goto!

 

[My Aswer Is This]

Re: How to create a list of all tables in a workbook

Try this:

Private Sub ListBox1_Click()
'Modified  12/21/2018  5:28:00 PM  EST
Dim ans As String
ans = ListBox1.Value
Dim pws As Worksheet, sws As String
sws = Range(ans).Parent.Name
If Sheets(sws).Visible = False Then Sheets(sws).Visible = True
Application.Goto Sheets(sws).Range(ans)
End Sub