VBA code to reset after execution or to end command

[Robert_G]

I have a spreadsheet where I copy and paste a comma separated text like this example:

24.034,6475.00,82.5,1945.18,1223.76,12.42,1,0,-37.1,
31.405,8454.00,83.3,1943.86,1937.10,12.41,1.5895,0,-0.3,
124.005,1562.00,83.2,90.96,89.30,0.58,1.5895,0,-1.8,
124.662,1562.00,83.2,90.48,89.03,0.58,1.5895,0,-1.6,



In my spreadsheet I have this code with to a command button that separates the data into columns with headings.



Private Sub CommandButton1_Click()


Dim objRange1 As Range


'Set up the ranges
Set objRange1 = Range("a23:a49")


'Do the first parse
objRange1.TextToColumns _
Destination:=Range("e2"), _
DataType:=xlDelimited, _
Tab:=False, _
Semicolon:=False, _
Comma:=True, _
Space:=False, _
Other:=False, _
OtherChar:="-"




End Sub




The problem I have is the code and command works great except if I copy and paste another comma separated text, it formats it automatically and places it in the wrong area.

I'm trying to figure out how to either to reset the code before I paste the new data or if it's going to automatically separate the data, is to have it at least format back to range E2 where I had specified.

 

[Robert_G]

bump

 

[Joe4]

See here: https://www.extendoffice.com/documents/excel/2511-excel-prevent-text-to-column.html

Looks like you can just add another Text-to-Column on some blank cell with no delimiters to reset it.

 

[shg]

Sub ResetTTC()
  On Error Resume Next
  With Cells(Rows.Count, Columns.Count)
    .Value = "Texas"
    .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
End Sub

 

[Robert_G]

Thank you! Worked like a charm.

 

[shg]

You're welcome.