when deleting table, filtered out items reappear in columns copied from table

[johnmerlino]

Hey all,

When I copy columns from a table to new columns, while it only copies over the filtered rows, when I delete the table, this forces the filtered out cells from the table to reappear in the newly created columns. This is not good because the whole purpose of filtering certain cells out was so I can isolate a list in a new column. Is there a way to break any link to table so I can only keep the copied values and not the hidden values that existed in the table?

Here's macro:

Sub sortify()
'
' sortify Macro
'
' Keyboard Shortcut: Ctrl+Shift+S
'
    Range("A1:E519").Select
    Selection.Cut Destination:=Range("A2:E520")
    Range("A2:E520").Select
    
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "A "
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "B"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "C"
    Range("D1").Select
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "D"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "E"
    Range("A1").Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$E$520"), , xlYes).Name = _
        "Table1"
    Range("Table1[#All]").Select
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight8"
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _
        "=Real Prop*", Operator:=xlAnd
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2, Criteria1:= _
        "=DF*", Operator:=xlAnd
        
    ActiveSheet.ListObjects("Table1").Sort.SortFields.Clear
    ActiveSheet.ListObjects("Table1").Sort.SortFields.Add _
        Key:=Range("Table1[[#All],[E]]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.ListObjects("Table1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("C:E").Select
    Selection.Cut
    Selection.SpecialCells(xlCellTypeVisible).Select
    Range("F1").Select
    ActiveSheet.Paste
End Sub

Thanks for any response.

 

[johnmerlino]

So there is no way to remove the association between table and contents that were copied from the table?