将表格转换为Excel表格（ListObject）

Option Explicit

Sub ConvertDataToTables()
 
    Const FIRST_CELL As String = "A2"
    Const FIRST_INDEX As Long = 3
    Const LAST_INDEX As Long = 5
     
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim ws As Worksheet, rg As Range, fCell As Range, lo As ListObject
    Dim i As Long, NewName As String
    
    For i = FIRST_INDEX To LAST_INDEX
        
        Set ws = wb.Worksheets(i)
        
        If ws.ListObjects.Count = 0 Then

            ' Clear any filters (including an advanced filter).                
            If ws.FilterMode Then ws.ShowAllData
            ' Remove the auto filter.
            If ws.AutoFilterMode Then ws.AutoFilterMode = False
            
            NewName = Replace(Application.Proper(ws.Name), " ", "")
            ws.Name = NewName
            
            Set fCell = ws.Range(FIRST_CELL)
            With fCell.CurrentRegion
                Set rg = fCell.Resize(.Row + .Rows.Count - fCell.Row, _
                    .Column + .Columns.Count - fCell.Column)
            End With
            
            Set lo = ws.ListObjects.Add(xlSrcRange, rg, , xlYes)
            lo.Name = NewName
            
        End If
        
    Next i
    
End Sub

试试下面的代码。它将替换工作表名称中的空格。而且，它不使用Select来依赖ActiveSheet -有关详细阅读，请参阅How to avoid using Select in Excel VBA
代码使用中间Range变量定义表格的范围。它从单元格A2（startCell）开始，并使用CurrentRegion的最后一个单元格作为endCell。

Dim sheetIndex As Long
For sheetIndex = 3 To ThisWorkbook.Worksheets.Count
    With ThisWorkbook.Worksheets(sheetIndex)
        If .ListObjects.Count = 0 Then
            Dim startcell As Range, endCell As Range, tableRange As Range
            Set startcell = .Cells(2, 1)
            Set endCell = startcell.CurrentRegion.Cells(startcell.CurrentRegion.Cells.Count)
            Set tableRange = .Range(startcell, endCell)
            Debug.Print tableRange.Address
            .ListObjects.Add(xlSrcRange, tableRange).Name = Replace(.Name, " ", "")
        End If
    End With
Next sheetIndex

请注意，您应该始终使用Option Explicit并声明所有变量，您应该从不使用On Error Resume Next，除非您知道它们可能会失败的单个语句（并且您希望自己进行错误处理）。