I am trying to create a very basic macro in Excel, but keeping coming
unstuck and hope someone can help.

I am trying to copy some text fields from an old DOS based programme
into Excel. If you simply copy and paste, the text will copy itself
into one cell only as a long string of text.

I want to separate this text into different columns; the manual way of
doing it is to click on paste options, use text import wizard, setting
the columns as fixed width. I have tried recording this as a macro,
but it is not working at all.

Any suggestions on a basic macro to copy this data in as separate
columns?

Thanks.

You need to paste it in first, then use data=>Text to Columns to do the same
as you describe. If do it that way with the recorder on, you will get the
code you need.

--
Regards,
Tom Ogilvy


"Moo" wrote:

> I am trying to create a very basic macro in Excel, but keeping coming
> unstuck and hope someone can help.
>
> I am trying to copy some text fields from an old DOS based programme
> into Excel. If you simply copy and paste, the text will copy itself
> into one cell only as a long string of text.
>
> I want to separate this text into different columns; the manual way of
> doing it is to click on paste options, use text import wizard, setting
> the columns as fixed width. I have tried recording this as a macro,
> but it is not working at all.
>
> Any suggestions on a basic macro to copy this data in as separate
> columns?
>
> Thanks.
>
>

Tom,

Thanks for your response. I've just tried that and it mainly works.
However, how do I get the pasting to go into the cells I've
highlighted (current cell), rather than cell A3, which is what I
recorded the original macro from (see code below):

ActiveSheet.Paste
Selection.TextToColumns Destination:=Range("A3"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(8, 1), Array(14, 1),
Array(21, 1), Array(26, 1), _
Array(53, 9), Array(55, 1)), TrailingMinusNumbers:=True
End Sub

I also keep getting the following message when running the macro, even
though the cells I'm pasting into are blank and I have turned off the
option to prompt when overwriting text! "Do you want to replace the
contents of the destination cells?"

Thanks.

Selection.TextToColumns Destination:=Range("A3"),

would become

Selection.TextToColumns Destination:=ActiveCell,


I suspect.

--
Regards,
Tom Ogilvy


"Moo" wrote:

> Tom,
>
> Thanks for your response. I've just tried that and it mainly works.
> However, how do I get the pasting to go into the cells I've
> highlighted (current cell), rather than cell A3, which is what I
> recorded the original macro from (see code below):
>
> ActiveSheet.Paste
> Selection.TextToColumns Destination:=Range("A3"),
> DataType:=xlFixedWidth, _
> FieldInfo:=Array(Array(0, 1), Array(8, 1), Array(14, 1),
> Array(21, 1), Array(26, 1), _
> Array(53, 9), Array(55, 1)), TrailingMinusNumbers:=True
> End Sub
>
> I also keep getting the following message when running the macro, even
> though the cells I'm pasting into are blank and I have turned off the
> option to prompt when overwriting text! "Do you want to replace the
> contents of the destination cells?"
>
> Thanks.
>
>