0

I have pasted a text file with fixed widths into column A. I tried recording the steps but when running the macro after repasting the text into column A get the following error- Run-time error 1004

Macro will be used to complete the same task on multiple files all with the same column spacing. Thanks for any help!

Sub Text2Columns()
    Columns("A:A").Select

    Selection.TextToColumns Destination:=Range("a3"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(48, 1), Array(65, 1), Array(88, 1), Array(110, 1), _
        Array(131, 1), Array(154, 1)), TrailingMinusNumbers:=True
    Columns("A:A").ColumnWidth = 12.86


End Sub
John Wiseman
  • 3,081
  • 1
  • 22
  • 31
Tyler
  • 1
  • 1

1 Answers1

0

I'm unclear on where .Range("A3") came from but as you are selecting the entire column to process (with A1 being the active cell), that is likely the root of your problem.

Sub Text2Columns()
    With Sheets("Sheet1")   '<-set to the worksheet!!!
        With .Columns("A:A")
            .TextToColumns Destination:=.Columns(1), DataType:=xlFixedWidth, _
                FieldInfo:=Array(Array(48, 1), Array(65, 1), Array(88, 1), Array(110, 1), _
                Array(131, 1), Array(154, 1)), TrailingMinusNumbers:=True
            .ColumnWidth = 12.86
        End With
    End With
End Sub

See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

Community
  • 1
  • 1