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

I'm confused as what sheet is the input and what is the output. What have you tried so far? If you are going from ws 1 to ws 2 I suggest you trim each value (not VBA version but rather worksheet function) then you can do SPLIT() on the single space that should be left. Then you can write to your target sheet. Please try to create a solution with this info and if you run into coding errors you can post them here. Regards, — nbayly, Oct 11 '17 at 19:05
*this has to be done in VBA* - without showing your attempts, you'll get little help. [how to ask](http://stackoverflow.com/help/how-to-ask) — Scott Holtzman, Oct 11 '17 at 19:09
It's a little difficult to show my code, as there is quite a bit... and I'd have to modify a lot of it because there's a lot of confidential information in it — Maldred, Oct 11 '17 at 19:10
I changed my question to make it a little easier to work with... my apologies! I also added some code I'm attempting to use — Maldred, Oct 11 '17 at 19:43
Alternative is to add as step which copy/moves rngSource values to Sheets("Consolidated_Data").Range("V3") then perform the text to columns there. See if issue is about the text to columns across different worksheets. In fact after testing myself it always moves to same sheet if you don't add a copy stage. This is also discussed in a possible duplicate question here: https://stackoverflow.com/questions/21865713/text-to-columns-vba-destination-onto-new-sheet — QHarr, Oct 11 '17 at 20:01

score 2 · Accepted Answer · answered Oct 11 '17 at 20:11

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

Text to Column to Different Sheet

1 Answers1