CODEWITHSUNDEEP
vbaexcelerror-handlingruntime-error
woodykiddy
woodykiddy

Reputation: 6455

Excel VBA run-time error 1004 'a table can't overlap another table'

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

Answers (2)

woodykiddy
woodykiddy

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

rayzinnz
rayzinnz

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

Related Questions