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:
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:
Then I've changed the String "Table13" to 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:
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