Text to Column to Different Sheet

Question

I'm trying to do a text to column from one sheet to another and the below code is not copying the data from the active sheet over to the destination... I am using code generated from the macro

Private Function ConvertText(rngSource As Range)

    rngSource.TextToColumns Destination:=Sheets("Consolidated_Data").Range("V3"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

End Function

Answer 1

The text to columns method it appears can't be used from one sheet to another, i.e.the destination has to be on the source data sheet. Though i can't find mention of this in the documentation.

So you would need to add some code which puts your sourceRange values into the target sheet at the top left of the range where you want to split the data out into columns i.e. "V3".

Sheets("Consolidated_Data").Range("V3") = sourceRange 'resize the Range V3 to size of sourceRange 
Sheets("Consolidated_Data").Range("V3").TextToColumns