VBA text to column with destination in other sheet

[kayza]

hi excel master
I have a VBA code like this

Application.DisplayAlerts = False
bs = 17
For i = 19 To 100 Step 6
Set obj = Sheets("[COLOR=#0000ff][B]AA[/B][/COLOR]").Cells(32, i)
    obj.TextToColumns _
    Destination:=Sheets("[COLOR=#ff0000][B]BB[/B][/COLOR]").Cells(bs, 6), DataType:=xlDelimited, OtherChar:="x"
    bs = bs + 1
Next

the code above does not work as I expected,
I want the source to come from sheet AA
but the destination is on sheet BB

How to do this?

Thank you in advance

 

[steve the fish]

You cant as far a im aware. Needs same sheet. Try this to see if it does the same:

bs = 17

With Sheets("AA")
    arr = .Range(.Cells(32, 19), .Cells(32, 100))
    For i = LBound(arr, 2) To UBound(arr, 2) Step 6
        For j = LBound(Split(arr(1, i), "x")) To UBound(Split(arr(1, i), "x"))
            Sheets("BB").Cells(bs, 6 + j) = Split(arr(1, i), "x")(j)
        Next
        bs = bs + 1
    Next
End With