Excel VBA 运行时错误 1004“一个表不能与另一个表重叠”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39584684/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA run-time error 1004 'a table can't overlap another table'
提问by woodykiddy
This run-time error, "a table can't overlap another table", occurs every time I open up my xlsm file. After I click through the debug window, the following line of VBA code is highlighted.
每次我打开我的 xlsm 文件时，都会发生这个运行时错误，“一个表不能与另一个表重叠”。在我点击调试窗口后，下面的 VBA 代码行被突出显示。
Set tbl = Sheets("DataSheet").ListObjects.Add(xlSrcRange, rng, , xlYes)
The full version is attached below just for your reference.
下面附上完整版，仅供参考。
Private Sub Workbook_Open()
Dim tbl As ListObject
Dim rng As Range
'Ungroup worksheets
ThisWorkbook.Sheets("DataSheet").Select
Set rng = Range(Range("A1"), Range("A1").SpecialCells(xlLastCell))
Set tbl = Sheets("DataSheet").ListObjects.Add(xlSrcRange, rng, , xlYes)
tbl.Name = "ReportTable"
tbl.TableStyle = "TableStyleMedium7"
End Sub
So what should I do to fix this issue? Why is giving an error on that line there?
那么我应该怎么做才能解决这个问题？为什么在那条线上出现错误？
回答by woodykiddy
The following is the solution that I ended up with. Seems to be solving the issue.
以下是我最终得到的解决方案。似乎正在解决问题。
Dim tblExists As Boolean
'Check the same already exists
tblExists = False
For Each o In Sheets("DataSheet").ListObjects
If o.Name = "ReportTable" Then tblExists = True
Next o
'If exists, delete the table first
If (tblExists) Then
Sheets("DataSheet").ListObjects("ReportTable").Unlist
End If
回答by rayzinnz
You could call this before adding the table, or modify it to be more specific:
您可以在添加表之前调用它，或者将其修改为更具体：
For Each tbl In Sheets("DataSheet").ListObjects
tbl.Unlist
Next
Note: tbl.Unlist will turn the table into a normal range but leave the data, tbl.Delete will remove a table and will also delete data within the table.
注意：tbl.Unlist 会将表格转为正常范围但保留数据，tbl.Delete 会删除一个表格，也会删除表格内的数据。
