textToColumns has to be on the source sheet AFAIK but you can replace the
last few lines

Range("I1:J150").Select
Selection.Copy
Sheets("Sheet3").Select
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


by:
With Range("I1:J150")
worksheets("sheet3").Range("G2").Resize( .Rows.Count,
..Columns.Count).Value = .Value
End With


"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