Excel macro loop code for more than one sheet

advertisements

I have code for delimited columns for one sheet. How do I make this code for more than sheets with looping?

Sub Macro1()

    Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="_", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
    Selection.NumberFormat = "dd-mm-yyyy;@"

End Sub


Sub t()
    Dim sh As Worksheet
    For Each sh In ActiveWorkbook.Sheets
        'do what you want, for example,
        Range("A1:B10").TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
            :="_", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
        ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
        Selection.NumberFormat = "dd-mm-yyyy;@"
    Next
End Sub

However, you should not use Selection. You should use a range instead, for e.g. Range("A1:B10")