For all named tables change name

[darryl47nopra]

I have a lot of tables located in the same spreedsheet (placed on different columns different rows), all named table1, table2 etc.

My goal is change the name to their respective "header" located 1 row above and 3 columns to the rigth of each table.

So far I got something like this (but it is not working)

Sub LoopThroughAllTablesWorksheet()


Dim ws As Worksheet
Dim tbl As ListObject

Set ws = ActiveSheet
For Each tbl In ws.ListObjects


    tbl.Names Names:=rngCell.Offset(-1, 2).Value
    

Next tbl

'End Sub
End Sub

 

[saboh12617]

Hello,
I think your code's structure is correct, however if you want to refer to the [-1,3] cell above the top left cell of the table, you should use

tbl.Name = tbl.Range.Item(1).Offset(-1, 3).Value

In your code it is not known what "rngCell" is.

 

[darryl47nopra]

Hello,
I think your code's structure is correct, however if you want to refer to the [-1,3] cell above the top left cell of the table, you should use

tbl.Name = tbl.Range.Item(1).Offset(-1, 3).Value

In your code it is not known what "rngCell" is.

I really don't know how to the define the "rngCell" since it is different for each table, any suggestion?

 

[saboh12617]

Did you try my proposition?

tbl.Range returns the range of the table, and tbl.Range.Item(1) the first cell in this range, ie. the top left one (as i wrote in my previous message).

 

[darryl47nopra]

Did you try my proposition?

tbl.Range returns the range of the table, and tbl.Range.Item(1) the first cell in this range, ie. the top left one (as i wrote in my previous message).

Sorry I miss it at firts, but I've tried and It worked perfectly!!

Thank you so much!!