Basic macro for copying into Excel
 

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.

Basic macro for copying into Excel
 

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.

Basic macro for copying into Excel
 

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.

Basic macro for copying into Excel
 

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.