-1

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?

Community
  • 1
  • 1
Maven
  • 14,587
  • 42
  • 113
  • 174
  • A range is in a sheet, not the opposite. Sheets cannot be in a range. You should rephrase your question – iDevlop Aug 30 '16 at 07:14
  • 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. – Maven Aug 30 '16 at 07:24

2 Answers2

1

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
iDevlop
  • 24,841
  • 11
  • 90
  • 149
  • Thankyou, I am able to get sheets through this but `ws.ListObjects.Count` remains zero. What constitutes as a table in excel, my sheets have data in cell and rows, that should be considered as table? – Maven Aug 30 '16 at 08:57
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
Christian Welsch
  • 424
  • 2
  • 10