Basic macro for copying into Excel

[Moo]

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.

 

[Guest]

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.

 

[Moo]

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.

 

[Guest]

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

would become

Selection.TextToColumns Destination:=ActiveCell,


I suspect.