Looping through sheets is not working

Question

I'm using the below code

Sub WMC()

Dim wb As Workbook
Set wb = Workbooks.Open(Filename:="G:\filedirectory.xls")

With wb

For i = 1 To Sheets.Count
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 4), TrailingMinusNumbers:=True
Next i

End With

ActiveWorkbook.Save
ActiveWorkbook.Close

End Sub

For some reason the loop runs but doesn't move from one sheet to the next - I think it just runs the same code on the first sheet in the workbook about ten times (as I have 10 sheets). If I remove the loop it runs once and closes so the code recognizes the loop, it just doesn't follow it. I've tried variations on the above but I get the same issue each time.

Any thoughts would be much appreciated.

Danke

Rachel

David Zemens · Accepted Answer · 2015-03-04T16:08:06.960

Your With block isn't qualifying the objects.

For i = 1 To Sheets.Count
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True
Next i

Uqualified objects like Columns("A:A") always refer to the ActiveSheet within the ActiveWorkbook. You have a With block, but you're not associating this object with that With block.

Try this:

For i = 1 To .Sheets.Count
    .Sheets(i).Columns("A:A").TextToColumns Destination:=.Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True
Next i

Or, my preference would be to just use For Each, there's no reason to do an indexed loop in this case:

Dim ws as Worksheet
With wb
    For each ws in .Worksheets
        ws.Columns("A:A").TextToColumns Destination:=ws.Range("A1"), ...


    Next
    .Save
    .Close
End With

Slight issue - you need to qualify the *destination range* parameter to `TextToColumns` with a sheet too. — Rory, Mar 04 '15 at 16:07
Thanks a lot, I was being v obtuse there! Will rerun with this Mon. — Rachel Dear, Mar 06 '15 at 20:40

score 1 · Answer 2 · answered Mar 04 '15 at 15:59

You're not specifying which sheet the code should work on and you need to qualify all the ranges:

Sub WMC()

Dim wb As Workbook
Set wb = Workbooks.Open(Filename:="G:\filedirectory.xls")

With wb

For i = 1 To .Sheets.Count
With .Sheets(i)
.Columns("A:A").TextToColumns Destination:=.Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 4), TrailingMinusNumbers:=True
End With
Next i

.Save
.Close
End With
End Sub

Looping through sheets is not working

2 Answers2