'Text to Columns' with destination in different workbook?

[DerekK]

Good day,

I've been trying for hours to figure this out (yes, including myriad Google search criteria permutations), I hope you can help!

I simply want to 'Text to Columns' with the cell which contains the data in one workbook but with the destination range in another workbook.

Although it the solution doesn't necessarily need to be in the form of VBA, I have made a rudimentary stab at it but it wants to make the destination range, the same sheet that contains the data cell.

Specifically, I need to start out in a cell inside a template workbook (A); select the cell which contains the data in another workbook (B); and have the destination be the original 'active' cell in the template workbook (B). There is a reason I need to do it this particular way but I'll spare you the details.

Here's the code I've been using, hopefully you can tell what I'm trying to do:

Sub Macro1()
'
' Macro1 Macro
'

'


Dim rbtDest As Range
Dim rbtCell As Range

Set rbtDest = Workbooks("Book1").Worksheets("Sheet2").Range(ActiveCell.Address)

Set rbtCell = Application.InputBox(prompt:="Select Region / Branch / Team cell", Title:="Region / Branch / Team", Type:=8)


    Application.CutCopyMode = False
    rbtCell.TextToColumns Destination:=rbtDest, DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="/", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
        TrailingMinusNumbers:=True
        
rbtDest.Select
        
rbtDest.Value = WorksheetFunction.Trim(rbtDest)


        
End Sub

I hope you can help! As, I'm sure, do my neighbors, who likely can hear the swearing.

Thanks,



Derek

 

[Russell Hauf]

Could you make the destination be in the same sheet, then cut/paste the data to the desired destination? If you are worried about overwriting data, you could open the data workbook read-only, then close it without saving.

 

[DerekK]

Thank you very much for the reply, Russell.

I'm afraid I can't make the destination in the same sheet because I wouldn't know where any available blank cells will be relative to the cell which contains the data I'm after, if that makes sense.

Here's my latest stab, which also isn't working, as it's returning a run-time error '13' type mismatch on the line I've pointed out:

Sub Macro1()
'
' Macro1 Macro
'

'


Dim rbtDest As Range
Dim rbtCell As Range
Dim rdString As String

Set rbtDest = ThisWorkbook.ActiveSheet.Range(ActiveCell, ActiveCell.Offset(, 2))

Set rbtCell = Application.InputBox(prompt:="Select Region / Branch / Team cell", Title:="Region / Branch / Team", Type:=8)

rdString = Split(rbtCell.Value, " / ") '''''THIS LINE RIGHT HERE

rbtDest = rdString

Set rbtDest = Nothing

Set rbtCell = Nothing


        
End Sub

 

[DerekK]

WHOOPS! Slight error. This works:

Sub Macro1()
'
' Macro1 Macro
'

'


Dim rbtDest As Range
Dim rbtCell As Range
Dim rdString() As String

Set rbtDest = ThisWorkbook.ActiveSheet.Range(ActiveCell, ActiveCell.Offset(, 2))

Set rbtCell = Application.InputBox(prompt:="Select Region / Branch / Team cell", Title:="Region / Branch / Team", Type:=8)

rdString = Split(rbtCell.Value, " / ")

rbtDest = rdString

Set rbtDest = Nothing

Set rbtCell = Nothing


        
End Sub

 

[thinhvd]

I think you should write a function by vba with other workbook destination

 

[Russell Hauf]

The split function is expecting its output to be an array, but I'm not sure what you're trying will work unless you make it a multi-dimensional array and split each cell (row).

You could try putting the output in the cell to the right of where it starts from, then close the workbook without saving it. In the past, I've even gone so far as to do my work on the original workbook...either opening it in read-only mode or closing it without saving it...then re-opening that workbook so I (or the user) can see it as it originally was (and the user didn't know that it ever closed).