Excel 2019
I am having an issue whereby Excel will not allow me to convert a range to a table and provides the "Run-time error '1004': A Table cannot overlap a range that contains a PivotTable report, query results, protected cells or another table caused by code line "Set tbl = ws.ListObjects.Add(xlSrcRange, selectedRange, , xlYes)"
Quick background.
I have VBA that imports a delimited text file.
The imported data is presented vertically my VBA selects it as a range which is then passed to a Power Query to remove unwanted lines of data and pivot the data horizontally.
On previous use this all worked fine until today for some reason Excel now gives the above error.
Nothing in the code was changed and I have been using the same test delimited text file so I have no clue what is going on.
I have written some code that is meant to clear all connections, tables and querys before the text file is imported which is below but the error still happens.
As the below images show, there are no connections, querys or tables in the workbook.
The wierd thing is that once I have ran the remove connections Sub and close and reopen the workbook my VBA to change the selected rang to Table works.
I really do not want to have to close and reopen the workbook each time I wish to create a new table.
If anyone can throw some light on this I would be very grateful as I am stumped.
This sub removes any data connections, querys and so on once the text file has been imported.
Sub RemoveConnections()
Dim connection As WorkbookConnection
Dim query As WorkbookQuery
Dim tbl As ListObject
Dim ws As Worksheet
Dim pt As PivotTable
' Enable error handling to capture errors
On Error Resume Next
' Delete all connections
For Each connection In ThisWorkbook.Connections
connection.Delete
Next
' Delete all PivotTables
For Each pt In ThisWorkbook.PivotTables
pt.TableRange2.Clear
Next
' Delete all queries
For Each query In ThisWorkbook.Queries
query.Delete
Next
' Delete all QueryTables
For Each QueryTable In ThisWorkbook.Sheets(1).QueryTables
QueryTable.Delete
Next
' Loop through each worksheet in the workbook
For Each ws In ThisWorkbook.Worksheets
' Remove any filters applied to the worksheet
ws.AutoFilterMode = False
' Convert all tables on the worksheet to ranges
End Sub
This code selects the range from the imported delimited text file.
Sub SelectRange()
Dim LastRow As Long
Dim ws As Worksheet
'Set reference to the "Data" worksheet
Set ws = ThisWorkbook.Sheets("Import")
'Find the last used row in column A
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
'Select range from A1 to B1 down to last used row in column A
ws.Range("A1:B1", ws.Cells(LastRow, "A")).Select
' Call RangeToTable
End Sub
Sub RangeToTable()
Dim ws As Worksheet
Dim tbl As ListObject
' Part 1: Get the pre-selected range
Set ws = ThisWorkbook.Sheets("Import")
Set selectedRange = Selection
' Remember the selected range as a variable
' Part 2: Convert selected range to a table
Set tbl = ws.ListObjects.Add(xlSrcRange, selectedRange, , xlYes)
' xlSrcRange specifies we are providing a range
' xlYes specifies the table has headers
tbl.Name = "Table1" ' Set the table name to "Table1"
End Sub
Excel error msg.jpg
Connections.jpg
Querys.jpg
Bookmarks