Use table name from current sheet

[gberg]

I have a macro that will be used on multiple sheets. Each sheet will have a Table (table will be the same size and have the same headers on each sheet). I want to run a macro that can be used on each sheet, my issue is that the tables will all have different names and I want to reference the table name for the current sheet. This is what I have tried (and failed) to come up with.

Sub Different_Tables()
Range("A1").Select
Dim ws As Worksheet
Dim tName As String

Set ws = ActiveSheet

tName = ActiveCell.ListObject.Name


Range("tName[[#Headers],[CATEGORY]]").Offset(1, 0).Select

End Sub

Hope someone can help

Thanks,

Greg

 

[Fluff]

Do you only have one table on each sheet?

 

[gberg]

Sorry, yes. There is only one table on each sheet

 

[JoeMo]

If only one (or none) per sheet try using list object index like this:

Sub SelectTable()
Dim tName As String
On Error Resume Next
tName = ActiveSheet.ListObjects(1).Name
If tName <> "" Then
    MsgBox ActiveSheet.Name & vbNewLine & tName
Else
    MsgBox "No table on sheet " & ActiveSheet.Name
End If
On Error GoTo 0
End Sub

 

[gberg]

How do I use that to reference the table? I want to select the first cell in one of the columns in the table

Range("tName[[#Headers],[CATEGORY]]").Offset(1, 0).Select

 

[jasonb75]

Avoiding the unnecessary selection, perhaps

With ActiveSheet.Range(tName & "[[#Headers],[CATEGORY]]").Offset(1, 0)

or maybe even

With ActiveSheet.Range(tName & "[CATEGORY]")(1)

 

[Fluff]

Another option

ActiveSheet.ListObjects(1).ListColumns("Category").Range(2).Select

 

[gberg]

Perfect, thank you all for your help!

 

[Fluff]

Glad we could help & thanks for the feedback.