You can use that Destination parm:

Worksheets("Sheet2").Range("A1:A150").TextToColumn s _
Destination:=Worksheets("sheet3").Range("G2"), _
DataType:=xlDelimited, _
textQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=True, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(Array(1, 9), Array(2, 9), Array(3, 1), Array(4, 1)), _
TrailingMinusNumbers:=True



Hazel wrote:

Hi All

I have just used the macro recorder for the code below is there a way to
send it to
sheet3 without copying and pasting.

Sub Tex2Col()

Sheets("Sheet2").Select
Range("A1:A150").Select
Selection.TextToColumns Destination:=Range("I1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 9), Array(2, 9), Array(3, 1), Array(4, 1)),
TrailingMinusNumbers:= _
True
Range("I1:J150").Select
Selection.Copy
Sheets("Sheet3").Select
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

--
Many Thanks

--

Dave Peterson