Going through all sheets in a workbook and iterate

Question

I have multiple sheets in a excel workbook, each sheet has a table. I want to select all sheets in an object so I can iterate through them by selecting tables in a set object one by one.

How can I do this?

Answer 1

Sub LoopThroughAllTablesinWorkbook()

    Dim tbl As ListObject
    Dim sht As Worksheet

    For Each sht In ThisWorkbook.Worksheets
        For Each tbl In sht.ListObjects

            'Your code here...

        Next tbl
    Next sht

End Sub

Answer 2

This should get you started:

Dim ws As Worksheet
Dim tbl As ListObject

For Each ws In ThisWorkbook.Worksheets
    For Each tbl In ws.ListObjects
        Debug.Print ws.Name, tbl.Name, tbl.Range.Address
    Next tbl
Next ws

I found this page and this one that cover the subject.

---

Edit: as you seem to have used Tables to speak about simple Ranges, here is a Range oriented alternative:

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    Debug.Print ws.Name, Range("A1").CurrentRegion.Address
Next ws