Reputation: 6455
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.
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?
Upvotes: 1
Views: 13139
Reputation: 6455
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
Upvotes: 4
Reputation: 1958
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.
Upvotes: 1