Loop a macro through all worksheets in an Excel workbook

Question

I am trying to run a macro through all worksheets in an Excel workbook. I have the code below, but it loops through only the first worksheet. The macro runs in the first worksheet again and again, instead of going on to the next worksheet like it should. Could someone please help? Below is my VBA code.

Sub WorksheetLoop()

     Dim WS_Count As Integer
     Dim I As Integer

     ' Set WS_Count equal to the number of worksheets in the active
     ' workbook.
     WS_Count = ActiveWorkbook.Worksheets.Count

     ' Begin the loop.
     For I = 1 To WS_Count

        ' Insert your code here.

 'lRow = .Range("A" & .Rows.Count).End(xlUp).Row
 Range("P4").Select
 ActiveCell.FormulaR1C1 = "=RC[-10]&"" ""&RC[-5]"
 Range("P4").Select
 Selection.AutoFill Destination:=Range("P4:P65536"), Type:=xlFillDefault
 Range("P4:P500").Select
 ActiveWindow.SmallScroll Down:=-24
 Selection.Copy
 Range("R4").Select
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
 Application.CutCopyMode = False
 ActiveSheet.Range("$R4:$R500").RemoveDuplicates Columns:=1, Header:=xlNo
 Selection.TextToColumns Destination:=Range("R4"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
    :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
 Range("U4").Select
 ActiveCell.FormulaR1C1 = "=INDEX(C[-16],MATCH(RC[-3],C[-15],0))"
 Range("V4").Select
 ActiveCell.FormulaR1C1 = "=INDEX(C[-12],MATCH(RC[-3],C[-11],0))"
 Range("U4:V4").Select
 Selection.AutoFill Destination:=Range("U4:V41"), Type:=xlFillDefault
 Range("U4:V500").Select

        ' The following line shows how to reference a sheet within
        ' the loop by displaying the worksheet name in a dialog box.

        'MsgBox ActiveWorkbook.Worksheets(I).Name

     Next I
    Exit Sub
  End Sub

You never actually use the the looping variable "i"; how is the code supposed to know that you want to reference each successive sheet? The problem is that your code doesn't specify a sheet at all - so it assumes you want to work on the active sheet. — Grade 'Eh' Bacon, Mar 02 '16 at 13:37

score 2 · Accepted Answer · answered Mar 01 '16 at 22:39

You need to actually change to each worksheet through each loop. You are basically just referencing the same one. Your code should look like:

Sub WorksheetLoop()
    Dim WS_Count As Integer
    Dim I As Integer

    ' Set WS_Count equal to the number of worksheets in the active
    ' workbook.
    WS_Count = ActiveWorkbook.Worksheets.Count

    ' Begin the loop.
    For I = 1 To WS_Count

        ' Insert your code here.
        Sheets(I).Select ' Added this command to loop through the sheets

        'lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        Range("P4").Select
        ActiveCell.FormulaR1C1 = "=RC[-10]&"" ""&RC[-5]"
        Range("P4").Select
        Selection.AutoFill Destination:=Range("P4:P65536"), Type:=xlFillDefault
        Range("P4:P500").Select
        ActiveWindow.SmallScroll Down:=-24
        Selection.Copy
        Range("R4").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
        ActiveSheet.Range("$R4:$R500").RemoveDuplicates Columns:=1, Header:=xlNo
        Selection.TextToColumns Destination:=Range("R4"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
        Range("U4").Select
        ActiveCell.FormulaR1C1 = "=INDEX(C[-16],MATCH(RC[-3],C[-15],0))"
        Range("V4").Select
        ActiveCell.FormulaR1C1 = "=INDEX(C[-12],MATCH(RC[-3],C[-11],0))"
        Range("U4:V4").Select
        Selection.AutoFill Destination:=Range("U4:V41"), Type:=xlFillDefault
        Range("U4:V500").Select

        ' The following line shows how to reference a sheet within
        ' the loop by displaying the worksheet name in a dialog box.

        'MsgBox ActiveWorkbook.Worksheets(I).Name

    Next I
    Exit Sub
End Sub

Have not checked validity of the rest of your code but the command I added will cycle through the sheets. Regards,

score 1 · Answer 2 · edited May 23 '17 at 11:50

You do not need to .Select or .Activate¹ a worksheet to process commands on it. Reference it with a With ... End With statement and preface all Range objects and Range.Cells properties with a period (e.g. .) to inherit the parent worksheet reference.

Sub WorksheetLoop()

    Dim lRow As Long, w As Long

    With ActiveWorkbook
        For w = 1 To .Worksheets.Count
            With .Worksheets(w)
                'the last row should be either from column F or K
                lRow = .Range("K" & .Rows.Count).End(xlUp).Row
                .Range("P4:P" & lRow).FormulaR1C1 = "=RC[-10]&CHAR(32)&RC[-5]"
                '.Range("P4:P" & lRow).Formula = "=F4&CHAR(32)&K4"
                With .Range("R4:R" & lRow)
                   .Value = .Range("P4:P" & lRow).Value  'direct value transfer is the preferred method for this
                   .RemoveDuplicates Columns:=1, Header:=xlNo
                   .TextToColumns Destination:=.Cells(1), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
                                  Semicolon:=False, Comma:=False, Space:=True, Other:=False, _
                                  FieldInfo:=Array(Array(1, 1), Array(2, 1))
                End With
                'R had duplicates removed; get the new last row
                lRow = .Range("R" & .Rows.Count).End(xlUp).Row
                .Range("U4:U" & lRow).FormulaR1C1 = "=INDEX(C[-16],MATCH(RC[-3],C[-15],0))"
                '.Range("U4:U" & lRow).Formula = "=INDEX(E:E, MATCH(R4, F:F, 0))"
                .Range("V4:V" & lRow).FormulaR1C1 = "=INDEX(C[-12],MATCH(RC[-3],C[-11],0))"
                '.Range("V4:V" & lRow).Formula = "=INDEX(J:J, MATCH(S4, K:K, 0))"

                With .Range("U4:V" & lRow)
                    'you left your code with columns U and V selected
                    'maybe more processing here like:
                    '.value = .value  '<~~ remove formulas to their values
                End With
            End With
        Next w
    End With

End Sub

Recorded macro code is very verbose. It is always a good idea to work through the code, remove useless code lines like ActiveWindow.SmallScroll Down:=-24 and make general improvements where you can.

¹ _{See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.}

score 0 · Answer 3 · answered Mar 01 '16 at 23:30

Don't loop through the count of the sheets, loop through the sheets.

Also get rid of all those activewindow.smallscroll lines you don't need them and remove the selects. Something like this:

Range("A1").Formula = "Hello" instead of Range("A1").Select Selection.formula = "Hello" Notice you can just delete Select and Selection

Here is an example of how to loop through the sheets:

Sub WS_Stuff()
Dim WS As Worksheet
For Each WS In Worksheets
    MsgBox WS.Name
Next
End Sub

Loop a macro through all worksheets in an Excel workbook

3 Answers3