Text To Columns VBA Destination onto new sheet

Question

I'm having a problem with Excel VBA Text To Columns. Apparently the function is fixated on putting the destination on the same sheet when I want it to be on a new sheet.

Worksheets("RawData").Columns(ID).TextToColumns Destination:=Worksheets("Regular").Columns("C"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=True, Space:=False, Other:=True, _
    OtherChar:="(", _
    FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True

Apparently this will just dump it on Column C of raw data rather than the worksheet of "Regular" where i want it to be.

Answer 1

Another approach would be using string manipulation directly. Assuming you want the colon to be your delimiter in the text to column command:

dim i as integer
dim j as integer
dim strTemp as string
dim arrString() as string

for i = 1 to 'number of rows in your first sheet
    strTemp = cells(i, 1)
    arrStrings = strings.split(strTemp, ",")
    for j = 1 to ubound(arrStrings)
        sheet2.cells(i, j) = arrStrings(j)
    next j
next i