How to reference table in a worksheet

[TheRedCardinal]

I have a Sub routine (thanks to prior help) that performs a set of tasks on a worksheet when the user closes it. There are 90 possible sheets.

On each sheet are three tables, but the way they have been created means their names are not predictable.

They are always in the same place. Top left cell of the header row is A5, L5 and S5 respectively.

How can I assign them to objects in my macro, so that I can manipulate them correctly. Specifically, I will want to clear the Data of each and write back the results of an Array calculation from my Macro. I also want to highlight some rows etc.

Could I allocate them to ListObjects my reference to location?

Thanks!

 

[RoryA]

Loop through the listobjects collection, and allocate each one to the relevant variable based on Listobject.Range.Cells(1).Address

 

[TheRedCardinal]

Hey Rory,
Thanks - and I love how you give the answer but leave some work to be done. Great for development!

I tried this:

Dim ImportTab As Object, ExportTab As Object, SummaryTab As Object, TableTest as Object

For Each TableTest In WS1.ListObjects

    If TableTest.Range.Cells(1).Address = WS1.Range("G5") Then Set ImportTab = TableTest
    If TableTest.Range.Cells(1).Address = WS1.Range("L5") Then Set ExportTab = TableTest
    If TableTest.Range.Cells(1).Address = WS1.Range("S5") Then Set SummaryTab = TableTest

Next TableTest

It loops through without error, but doesn't set any of the tables correctly.

I'm mixing up references I think?

 

[RoryA]

You're comparing an address to a cell value. Use:

    If TableTest.Range.Cells(1).Address = "$G$5" Then
       Set ImportTab = TableTest
    ElseIf TableTest.Range.Cells(1).Address = "$L$5" Then
         Set ExportTab = TableTest
    ElseIf TableTest.Range.Cells(1).Address = "$S$5" Then
       Set SummaryTab = TableTest
    End If

 

[RoryA]

You could also use:

    Select Case TableTest.Range.Cells(1).Address
       Case "$G$5"
          Set ImportTab = TableTest
       Case "$L$5" 
          Set ExportTab = TableTest
      Case "$S$5"
          Set SummaryTab = TableTest
    End Select

 

[TheRedCardinal]

Wonderful thanks!