Loop Through Multiple Worksheets Starting From Specific Sheet Index

Question

I'm trying to loop through all sheets in a workbook, starting with the 4th tab. This is what I have, but for some reason it only works on the 4th tab, and doesn't continue with the rest. Any ideas?

Sub Step13()

Dim sheet As Worksheet
For Each sheet In ActiveWorkbook.Worksheets
  If sheet.Index > 3 Then

'my code here 
Columns("A").Replace "^", vbNullString, xlPart, xlByRows, True
Range("AA1").Value = "Function"
Dim LastRowColumnA As Long
LastRowColumnA = Cells(Rows.Count, 1).End(xlUp).Row
Range("AA2:AA" & LastRowColumnA).FormulaR1C1 = "=ISNUMBER(MATCH(RC[-26],EarningsDates!C[-26],0))"
Columns(27).Value = Columns(27).Value

  End If
Next

End Sub

Code works for me. Is there an `Exit For` in the if condition (the code you have not shown)? — Storax, Aug 11 '19 at 20:05
You can edit your post accordingly. But it seems your references are wrong. — Storax, Aug 11 '19 at 20:28
You need to refer to the sheet with sheet. in front of the ranges etc. — Storax, Aug 11 '19 at 20:42
Thanks for the tip! Changed code as per answer below. Works perfectly now. — Gitty, Aug 11 '19 at 20:58

score 0 · Accepted Answer · answered Aug 11 '19 at 20:59

Below is the corrected code. Works perfectly.

Sub Step13()

Dim sheet As Worksheet
For Each sheet In ActiveWorkbook.Worksheets
  If sheet.Index > 3 Then

sheet.Activate

'my code here 
ActiveSheet.Columns("A").Replace "^", vbNullString, xlPart, xlByRows, True
ActiveSheet.Range("AA1").Value = "Function"
Dim LastRowColumnA As Long
LastRowColumnA = Cells(Rows.Count, 1).End(xlUp).Row
ActiveSheet.Range("AA2:AA" & LastRowColumnA).FormulaR1C1 = "=ISNUMBER(MATCH(RC[-26],EarningsDates!C[-26],0))"
ActiveSheet.Columns(27).Value = Columns(27).Value

  End If
Next

End Sub

score 0 · Answer 2 · answered Aug 11 '19 at 21:40

I think you will find the below code enhances the performance of your routine for two reasons:

It works directly with the needed object. (and doesn't activate each sheet each time, which is not necessary
It only works specifically with cells with data (and not entire columns, most rows of which are not necessary.

Code:

Option Explicit

Sub Step13()

    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets

        If ws.Index > 3 Then

            With ws

                Dim LastRowColumnA As Long
                LastRowColumnA = .Cells(.Rows.Count, 1).End(xlUp).Row

                'my code here
                .Range("A1:A" & LastRowColumnA).Replace "^", vbNullString, xlPart, xlByRows, True

                .Range("AA1").Value = "Function"
                With .Range("AA2:AA" & LastRowColumnA)
                    .FormulaR1C1 = "=ISNUMBER(MATCH(RC[-26],EarningsDates!C[-26],0))"
                    .Value = .Value
                End With

            End With

        End If

    Next

End Sub

score 0 · Answer 3 · answered Aug 11 '19 at 21:46

The reason you were having issues is that you had not fully qualified your ranges, and your code is only working on the active sheet. The answer to your problem is to fully qualify any reference to a range to ensure the code properly understands which cell, range, sheet or workbook you are referring to.

Below is corrected code from Gilty to remove bad programming practices. Not tested.

Option Explicit ' <-- always include this at the top of modules.

' Proper indenting helps make code readable and maintainable.
Sub Step13()
    Dim sheet As Worksheet
    For Each sheet In ActiveWorkbook.Worksheets
        If sheet.Index > 3 Then
        ' Do not use .Select or .Activate unless you want the user to see something.
        ' Fully qualify all ranges.
            sheet.Columns("A").Replace "^", vbNullString, xlPart, xlByRows, True
            sheet.Range("AA1").Value = "Function"
            Dim LastRowColumnA As Long
            LastRowColumnA = sheet.Cells(sheet.Rows.Count, 1).End(xlUp).Row '<-- Fully qualify ALL references to ranges.
            sheet.Range("AA2:AA" & LastRowColumnA).FormulaR1C1 = "=ISNUMBER(MATCH(RC[-26],EarningsDates!C[-26],0))"
            sheet.Columns(27).Value = sheet.Columns(27).Value '<-- Fully qualify ALL references to ranges.
        End If
    Next
End Sub

Now this can be a little neater by using With.

Option Explicit

Sub Step13()
    Dim sheet As Worksheet
    For Each sheet In ActiveWorkbook.Worksheets
        With sheet
            If .Index > 3 Then
                .Columns("A").Replace "^", vbNullString, xlPart, xlByRows, True
                .Range("AA1").Value = "Function"
                Dim LastRowColumnA As Long
                LastRowColumnA = .Cells(.Rows.Count, 1).End(xlUp).Row 
                .Range("AA2:AA" & LastRowColumnA).FormulaR1C1 = "=ISNUMBER(MATCH(RC[-26],EarningsDates!C[-26],0))"
                .Columns(27).Value = .Columns(27).Value 
            End If
        End With
    Next
End Sub

Loop Through Multiple Worksheets Starting From Specific Sheet Index

3 Answers3