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