获取运行时 1004：使用单元格时对象"_Worksheet"的方法"范围"失败

Question

Sub WorksheetLoop()
Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range
Dim objTable As ListObject
Dim WS_Count As Integer
Dim I As Integer
WS_Count = ActiveWorkbook.Worksheets.Count

For I = 1 To WS_Count
Set sht = Worksheets(I)
Set StartCell = Range("A2")
LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column
' error on next line
sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select
Set objTable = ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes)
MsgBox ActiveWorkbook.Worksheets(I).Name
Next I
End Sub

Answer 1

试试这个，修改后的代码版本：

Sub WorksheetLoop()
Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim rngStart As Range
Dim rngTolist As Range
Dim objTable As ListObject
For Each sht In ActiveWorkbook.Worksheets
Set rngStart = sht.Range("A2")
LastRow = sht.Cells(sht.Rows.Count, rngStart.Column).End(xlUp).Row
LastColumn = sht.Cells(rngStart.Row, sht.Columns.Count).End(xlToLeft).Column
Set rngTolist = sht.Range(rngStart, sht.Cells(LastRow, LastColumn))
Set objTable = sht.ListObjects.Add(xlSrcRange, rngTolist, , xlYes)
MsgBox sht.Name & ", " & objTable.Name
Next
End Sub