Error "1004 a table cannot overlap another table"

maglorluinwe

New Member
Joined
Mar 18, 2016
Messages
1
Hello everyone.
I just recorded a Macro that copies a table from a spreadsheet and pastes it on another cell. It turned out that it gives the error "1004 a table cannot overlap another table". I figured out that it is happening because the table name is fixed to a String that does not vary by debbugging the code:

Code:
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$L$7:$M$8"), , xlYes).Name = "Table13"

As there is already a table with that name, the error occurs. For that matter, I manually changed it to "Table50" and the error does not occur anymore (but it would be nice to automate this step...)

In an attempt to make my life easier, I've created a String variable:

Code:
Dim Variable As String
    Variable = InputBox("Enter the table name")

Then I've changed the String "Table13" to Variable:

Code:
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$L$7:$M$8"), , xlYes).Name = Variable

When I execute the code, it runs and a window pops up asking me to give a name; however, it does not matter the name I give it, the error keeps on happening; I tried to name it Table60, dog, Dog, Dinossaur, IceCream50... the error keeps on happening...

Any ideas of how could I make it work? Thanks!
The full VBA code is below:


Code:
Sub InserirTabela()
'
' InserirTabela Macro
'

'
    Dim Variable As String
    Variable = InputBox("Enter the table name")
    
    ActiveCell.Offset(-5, 0).Range("A1:B3").Select
    Selection.Copy
    ActiveCell.Offset(5, 0).Range("Alpha[[#Headers],[Nome completo]]").Select
    ActiveSheet.Paste
    ActiveCell.Offset(1, 0).Range("A1:B2").Select
    Application.CutCopyMode = False
    Selection.ClearFormats
    Variavel = ActiveSheet.Name
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$L$7:$M$8"), , xlYes).Name = Variable
    ActiveCell.Range("A1:B2").Select
    ActiveSheet.ListObjects(Variable).ShowTotals = True
    ActiveCell.Offset(1, 0).Range("Alpha[[#Headers],[Nome completo]]").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=topicos"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    ActiveCell.Offset(-2, 0).Range( _
        "Alpha[[#Headers],[Nome completo]:[Nota da Prova]]").Select
    Selection.ClearContents
End Sub
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Forum statistics

Threads
1,223,275
Messages
6,171,121
Members
452,381
Latest member
Nova88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top