For Each Worksheet Text to Columns Loop Issue

[chirayu]

Hi All,

For some reason my code is not working on each sheet in the workbook.

It throws up a popup box saying do I want to replace contents & then only works on the activesheet and ends.

Sub Macro1()
 
  If ActiveSheet.Name = "Home" Then
  End
  Else
  Dim ws As Worksheet
  For Each ws In ActiveWorkbook.Worksheets
  With ws
  Columns("A:A").Select
  Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
  FieldInfo:=Array(Array(0, 1), Array(35, 1), Array(48, 1)), TrailingMinusNumbers _
  :=True
  Range("A1").Select
  End With
  Next ws
  End If
 
End Sub

 

[Luke M]

Remember that when using 'with' statements, need to include a period in front of objects so that they trace back to With object. Otherwise, they will use ActiveSheet. Both "Columns" and "Range" are missing the leading periods.
Corrected code:

Sub Macro1()
Dim ws As Worksheet
If ActiveSheet.Name = "Home" Then
    End
Else
    For Each ws In ActiveWorkbook.Worksheets
        With ws
            'Since we're not selecting the sheet (nor do we want to)
            'don't try to select range
            .Columns("A:A").TextToColumns Destination:=.Range("A1"), DataType:=xlFixedWidth, _
                FieldInfo:=Array(Array(0, 1), Array(35, 1), Array(48, 1)), TrailingMinusNumbers _
                :=True
        End With
    Next ws
End If

End Sub