VBA to get and use a table name

[gberg]

I want to use the same VBA on multiple worksheets. Each worksheet has a table so I need to get the table name and then use that name for each worksheet

I have this to get the table name
Range("A12").Select
TableName = ActiveCell.ListObject.Name

I tried doing this but got an error
Range("TableName[[#Headers],[Area]]").Offset(1, 0).PasteSpecial Paste:=xlPasteValues

Any help would be appreciated

Thanks,

Greg

 

[RoryA]

You could just use the table object:

Range("A12").Listobject.Listcolumns("Area").DataBodyRange.Cells(1).PasteSpecial Paste:=xlPasteValues

 

[gberg]

RoryA,

I actually don't even like the reference to cell A12 to get the table name. I'm worried that the table will shift an the A12 reference will no longer be good (still trying to figure that out).

With that I don't like referencing A12 in the solution.

I played around a bit and was able to figure the second part out with this

Range(TableName & "[[#Headers],[Area]]").Offset(1, 0).PasteSpecial Paste:=xlPasteValues

Thanks for the suggestion

 

[Georgiboy]

Could call the table by its index number instead if there is only one table in each sheet or if it is always the first table.

Sub test2()
    Dim ws As Worksheet
    
    Sheet3.Range("A2:E2").Copy
    
    For Each ws In ThisWorkbook.Worksheets
        ws.ListObjects(1).ListRows(1).Range.PasteSpecial xlValue
    Next ws
    
End Sub