writing a loop to parse multiple sheets.

**ynnod** · 09-19-2007, 11:33 AM

Hello,

i'm looking for help writing a loop that will perform the same action on 11 worksheets but stop if it gets to a sheet with no data in cell A1. The data in A1 will be text. The code I wrote below below does what I want it to, but I have to run it on each individual sheet. Any help would be greatly appreciated.

~Donny

 
Sub parse()
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(2, 1), Array(15, 1), Array(17, 1), Array(24, 1), _
        Array(25, 1), Array(32, 1), Array(33, 1), Array(40, 1), Array(42, 1), Array(50, 1), Array( _
        52, 1), Array(59, 1), Array(60, 1), Array(68, 1), Array(69, 1), Array(77, 1), Array(79, 1)) _
        , TrailingMinusNumbers:=True
    Range("B12").Select
End Sub

**VBA Noob** · 09-19-2007, 11:41 AM

Does this help

Sub loopSheets()
Dim wsSheet As Worksheet

    For Each wsSheet In Worksheets
      MsgBox wsSheet.Name ' add your code here
    Next wsSheet

End Sub

VBA Noob

**ynnod** · 09-20-2007, 12:46 PM

Thanks for the help, but this doesn't work.

Any other ideas?

~Donny

**Norie** · 09-20-2007, 02:16 PM

Donny

Try this.

Sub ParseWS()
Dim ws As Worksheet
    
    For Each ws In Worksheets
        With ws
            If .Range("A1") <> "" Then
                .Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
                FieldInfo:=Array(Array(0, 1), Array(2, 1), Array(15, 1), Array(17, 1), Array(24, 1), _
                Array(25, 1), Array(32, 1), Array(33, 1), Array(40, 1), Array(42, 1), Array(50, 1), Array( _
                52, 1), Array(59, 1), Array(60, 1), Array(68, 1), Array(69, 1), Array(77, 1), Array(79, 1)) _
                , TrailingMinusNumbers:=True
        End With
    Next ws
End Sub

**ynnod** · 09-20-2007, 05:07 PM

I think this will work, but I think I need to tell it what range of worksheets to parse as I don't want it to parse every worksheet in the workbook. Sorry, I should have been more clear in my description.

~Donny