VBA Code Revision - define range as table in multiple tabs/worksheets

[AK12]

Hi,

The following code works to define ranges in 200 plus tabs in a workbook but it names each table Table1, 2, 3, and so on.

can any one revise the code to provide the worksheet name as table name instead please?

Thank you

Sub AddTablesIfNone()
Dim ws As Excel.Worksheet
For Each ws In ActiveWorkbook.Worksheets
        With ws
            If .UsedRange.ListObject Is Nothing Then
                .ListObjects.Add SourceType:=xlSrcRange, Source:=.UsedRange, xllistobjecthasHeaders:=xlYes
            End If
        End With
    Next ws
    End Sub

>>> use code - tags <<<​

 

[p45cal]

untested:

Sub AddTablesIfNone()
Dim ws As Excel.Worksheet
For Each ws In ActiveWorkbook.Worksheets
  With ws
    If .UsedRange.ListObject Is Nothing Then
      With .ListObjects.Add(SourceType:=xlSrcRange, Source:=.UsedRange, xllistobjecthasHeaders:=xlYes)
        On Error Resume Next 'in case there's already a table with the same name.
        .Name = ws.Name
        On Error GoTo 0
      End With
    End If
  End With
Next ws
End Sub

 

[AK12]

Thank you so much!!!!

I haven't checked all the tabs but it seems to be working. You are awesome!