Text to columns to a different sheet using VBA.!?

[Bumba]

Hi people,

How do I split text from a range in a column (maybe using text to column, separator "x") to a different worksheet and rearrange. To make you understand better here is a image of the source data(Sheet1)

My expected output is something like(Sheet2):

I've done

Sub customSplit()
    Dim objRange1 As Range
    
    Set objRange1 = Range("A2:A6")
    
    objRange1.TextToColumns _
      Destination:=ActiveWorkbook.Worksheets("Sheet2").Range("F2"), _
      DataType:=xlDelimited, _
      Tab:=False, _
      Semicolon:=False, _
      Comma:=False, _
      Space:=False, _
      Other:=True, _
      OtherChar:="x"

End Sub

But it pastes the result in Sheet1 & also I cannot find any option to rearrange the data to my needs.
Please help me!!

 

[Fluff]

How about

Sub Bumba()
    Dim Cl As Range
    Dim Sp As Variant
    
    With Sheets("Sheet1")
        For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
            Sp = Split(Cl.Value, "x")
            Sheets("Sheet2").Range("F" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Sp(2), Sp(0), Sp(1))
        Next Cl
    End With
End Sub

 

[Bumba]

Thank you.
BTW, can you explain the below line in your code:
Sheets("Sheet2").Range("F" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Sp(2), Sp(0), Sp(1))

 

[Fluff]

That line find the last used row in col F, offsets down 1 line, then resizes by 3 columns.
So if F1 has a value it would give a range of F2:H2 then it puts the values in the Sp array into those cells with the last value of the array going into H the first value into G & the middle value into H
HTH

 

[Bumba]

Thanks again for explaining...One last thing out of curiosity though, if I wanted the values in Sheet2 columns say F,H & J instead of F,G & H how can I do that?

Also, How do I mark this post as solved or answered ?

 

[Fluff]

if I wanted the values in Sheet2 columns say F,H & J instead of F,G & H how can I do that

You would have to do it individually for each cell.

We don't mark threads as solved here.

 

[Bumba]

Well, if I do it like below it messes things up:

            Sheets("Sheet2").Range("F" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Sp(2))
            Sheets("Sheet2").Range("H" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Sp(0))
            Sheets("Sheet2").Range("J" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Sp(1))

What am I doing wrong?

 

[Fluff]

It should either be like

Sheets("Sheet2").Range("F" & Rows.Count).End(xlUp).Offset(1) = Sp(2)

or slightly simpler

            With Sheets("Sheet2").Range("F" & Rows.Count).End(xlUp).Offset(1)
                .Value = Sp(2)
                .Offset(, 2).Value = Sp(0)
               .Offset(, 4).Value = Sp(1)
            End With

 

[Bumba]

Thanks a lot.

 

[Fluff]

You're welcome & thanks for the feedback