VBA text to column with destination in other sheet

kayza

Board Regular
Joined
Apr 29, 2015
Messages
53
hi excel master
I have a VBA code like this
Code:
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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,110
Office Version
  1. 365
Platform
  1. Windows
You cant as far a im aware. Needs same sheet. Try this to see if it does the same:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,806
Messages
5,524,983
Members
409,613
Latest member
Dalex100

This Week's Hot Topics

Top