For Each/Next Loop does not walk through all worksheets

Question

I have created follwoing code. The macro should simply multiply the cells A2:G3000 by 1 so that the format changes from a text to a number. The macro I wrote does so, but only for the active worksheet.. I used the For Each/Next loop as I learned it.

Could somebody help me find my mistake in the code?

Sub Format_Change()

Dim sht As Worksheet

For Each sht In Worksheets
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "=RC[-12]*1"
    Range("M2").Select
    Selection.AutoFill Destination:=Range("M2:W2"), Type:=xlFillDefault
    Range("M2:W2").Select
    Selection.AutoFill Destination:=Range("M2:W3000"), Type:=xlFillDefault
    Range("M2:W3000").Select
    Selection.Copy
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("M2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False
    Selection.ClearContents
Next sht

End Sub

score 5 · Accepted Answer · answered Apr 13 '17 at 08:16

5

Try the code below, as @Vityata noted, there's realy no need to use so many Select and Selection, it slows the code down a lot.

Try the code version below:

Option Explicit

Sub Format_Change()

Dim sht As Worksheet

For Each sht In Worksheets
    With sht
        .Range("M2").FormulaR1C1 = "=RC[-12]*1"
        .Range("M2").AutoFill Destination:=.Range("M2:W2"), Type:=xlFillDefault
        .Range("M2:W2").AutoFill Destination:=.Range("M2:W3000"), Type:=xlFillDefault
        .Range("M2:W3000").Copy
        .Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
         Application.CutCopyMode = False
        .Range(.Range("M2"), .Range("M2").CurrentRegion).ClearContents
    End With
Next sht

End Sub

answered Apr 13 '17 at 08:16

Shai Rado

33,032
6
29
51

1

+1, but what if the OP wanted to see actually the pages changing and the job being done? It gives a nice feeling somehow to have an Excel file "working" ... Without the `select` you do not see the `action` :) – Vityata Apr 13 '17 at 08:26
2

@Vityata Yeah you are sitting in front of your front-loading washer and microwave just watching them to finish? – Pᴇʜ Apr 13 '17 at 08:29
1

Different people, different hobbies - https://i.ytimg.com/vi/BdjaJgQU85g/hqdefault.jpg – Vityata Apr 13 '17 at 08:30
1

@Vityata that's what we need here, some sense of Humor – Shai Rado Apr 13 '17 at 08:31
@ShaiRado actually Im just recording the makros, thats why I got so many "seletct or selection". Your code works as I imagined mine to work, Thank you very much! – user7761353 Apr 13 '17 at 09:26

score 2 · Answer 2 · edited May 23 '17 at 12:32

In general the code is not good, because it uses too much select and etc. Read here How to avoid using Select in Excel VBA macros. However, if you want to make it a running one, just add sht.Select after the for-each loop.

Option Explicit


Sub Format_Change()

    Dim sht As Worksheet

    For Each sht In Worksheets
        sht.Select
        Range("M2").Select
        ActiveCell.FormulaR1C1 = "=RC[-12]*1"
        Range("M2").Select
        Selection.AutoFill Destination:=Range("M2:W2"), Type:=xlFillDefault
        Range("M2:W2").Select
        Selection.AutoFill Destination:=Range("M2:W3000"), Type:=xlFillDefault
        Range("M2:W3000").Select
        Selection.Copy
        Range("A2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("M2").Select
        Range(Selection, Selection.End(xlDown)).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Application.CutCopyMode = False
        Selection.ClearContents
    Next sht
End Sub

OK, +1, but my OCD didn't let me not give an answer without using the `Select`, hope you forgive me ;) — Shai Rado, Apr 13 '17 at 08:17
@Vityata Thanks for the suggestion, I´ll read through. Since I am yet only recording macros that could really help me a lot, thank you for that one! — user7761353, Apr 13 '17 at 09:28

For Each/Next Loop does not walk through all worksheets

2 Answers2