TextToColumns issue

[Formula11]

Data from other programs is often a delimited type as seen below in yellow cells. This comes from a text export in this instance.
Code below splits data into columns. This part works.

After the macro is run, for some reason when copying and pasting data which is in tabular format in the other program (not delimited/text), the paste is not as expected.
Thought that a setting is changed from code below. Tried to use Application.CutCopyMode but does not work.
If Excel is shut down and restarted and the source data from other program is copied and pasted, the paste into Excel works as expected.

Sub Split_to_columns()
    Dim MyRange As Range
    Set MyRange = Range("A4:A100")
    'Split data into columns
    Application.CutCopyMode = False
    MyRange.TextToColumns Destination:=MyRange.Offset(0, 1), DataType:=xlDelimited, ConsecutiveDelimiter:=True, Space:=True
    Application.CutCopyMode = True
End Sub

 

[Joe4]

Is this, perhaps, what you are experiencing?

Stop automatic text-to-columns formatting

This morning I was pasting text into excel and using Text-to-Columns to parse it. Later, in a different file, I was pasting other text that I did not want..

techcommunity.microsoft.com

 

[Formula11]

Thanks Joe4, this worked.

Code below is based on links followed.

Sub reset_ttc()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    On Error Resume Next
    'Temporary workbook
    Workbooks.Add
    With Cells(1, 1)
        .Value = "Any text"
        .TextToColumns Destination:=.Cells, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, TrailingMinusNumbers:=True, FieldInfo:=Array(1, 1)
        .Clear
        .Worksheet.UsedRange
    End With
    ActiveWorkbook.Close False
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

 

[Joe4]

You are welcome.
Glad we were able to help!