Loop through the workbook but exclude some worksheets?

Question

This question has been asked around the net several times but without a comprehensive program in within the macro. I have data on some sheets but I need to analyse that data more specifically on some separate 'Analysis sheets'. I believe the problem may be to do with ThisWorkbook.Sheets("sh") function as it gives off a subscript out of range error.

Below is the code, respond as to any changes that you would make to make this code work!

Sub Excludesheet()
    Dim sh As Worksheet
    Const excludeSheets As String = "1-Analysis,2-Analysis"
    'Assigns a Worksheet Object to the sh variable
    For Each sh In ThisWorkbook.Worksheets
        If IsError(Application.Match(sh.Name, Split(excludeSheets, ","))) Then
        'This is for analysis worksheets
        Range("$A$1").Value = "Analysis"
        Else:
            'Data Sheets
            Columns("A:M").AutoFit
            LR = Cells(Rows.Count, "A").End(xlUp).Row
            For i = LR To 2 Step -1
                If Cells(i, "E").Text = "N/A" Then Rows(i).EntireRow.Delete
            Next i

            LastR = Cells(Rows.Count, "A").End(xlUp).Row
            Dim strFormulas(1 To 3) As Variant

            With ThisWorkbook.Sheets("sh")
                strFormulas(1) = "=(E2-$E$2)"
                strFormulas(2) = "=(G2-$G$2)"
                strFormulas(3) = "=H2+I2"

                Range("H2:J2").Formula = strFormulas
                Range("H2:J" & LastR).FillDown
            End With
        End If
    Next
End Sub

`With ThisWorkbook.Sheets("sh")` to `With sh` – L42 Apr 25 '15 at 00:26 — L42, Apr 25 '15 at 00:26

score 1 · Accepted Answer · edited May 23 '17 at 11:51

To further clarify my comment, work on your objects directly.
Check this out for various ways on how you'll do that.

Now try this refactored code of yours:

Sub Excludesheet()
    Dim sh As Worksheet
    'Const excludeSheets As String = "1-Analysis,2-Analysis"
    Dim excludeSheets: excludeSheets = Array("1-Analysis", "2-Analysis")
    For Each sh In ThisWorkbook.Worksheets
        With sh 'you already have the sheet object, so work with it
            If Not IsError(Application.Match(.Name, excludeSheets, 0)) Then
                'This is for analysis worksheets
                .Range("$A$1").Value = "Analysis"
            Else
                'Data Sheets
                .Columns("A:M").AutoFit
                LR = .Cells(.Rows.Count, "A").End(xlUp).Row

                For i = LR To 2 Step -1
                    If .Cells(i, "E").Text = "N/A" Then .Rows(i).EntireRow.Delete
                Next i

                LastR = .Cells(.Rows.Count, "A").End(xlUp).Row
                Dim strFormulas(1 To 3) As Variant

                strFormulas(1) = "=(E2-$E$2)"
                strFormulas(2) = "=(G2-$G$2)"
                strFormulas(3) = "=H2+I2"

                .Range("H2:J2").Formula = strFormulas
                .Range("H2:J" & LastR).FillDown
            End If
        End With
    Next
End Sub

This one worked, just had to directly reference a few objects as in sh.range sh.cells sh.collumns instead of direct referencing cells as @Comintern had suggested. — Thomas Ruddell, Apr 28 '15 at 02:20

score 1 · Answer 2 · answered Apr 25 '15 at 00:34

I would use a With ... End With block to define the parent of all of the cell and range references.

Sub Excludesheet()
    Dim i As Long, lr As Long, sh As Worksheet
    Const excludeSheets As String = "1-Analysis,2-Analysis"
    For Each sh In ThisWorkbook.Worksheets  'Assigns a Worksheet Object to the sh variable
        With sh
            If CBool(InStr(1, excludeSheets, .Name, vbTextCompare)) Then
                'This is for analysis worksheets
                 .Range("$A$1").Value = "Analysis"
            Else
                'Data Sheets
                .Columns("A:M").AutoFit
                lr = .Cells(Rows.Count, "A").End(xlUp).Row
                For i = lr To 2 Step -1
                    If .Cells(i, "E").Text = "N/A" Then Rows(i).EntireRow.Delete
                Next i

                lr = .Cells(Rows.Count, "A").End(xlUp).Row
                Dim strFormulas(1 To 3) As Variant

                strFormulas(1) = "=(E2-$E$2)"
                strFormulas(2) = "=(G2-$G$2)"
                strFormulas(3) = "=H2+I2"

                .Range("H2:J2").Formula = strFormulas
                .Range("H2:J" & lr).FillDown
            End If
        End With
    Next sh

End Sub

I've also reused some vars so new ones did not have to be declared and changed your method of determining the name category of the worksheet. Your existing method seemed backwards to me (e.g. if not found then) so I reversed the logic. If my assumption is wrong you can reverse my logic with If NOT CBool(InStr(....

I was thinking of using `InStr` as well but decided to just abandon the constant string. haha — L42, Apr 25 '15 at 00:47
Jeeped and L42 both of your codes work across multiple sheets and correctly puts analysis in correctly at the correct worksheet, but some parts of the macro are excluded on the following sheets. The delete "N/A" function works well on the first sheet for the following sheets it does not perform. Is there a way around that? — Thomas Ruddell, Apr 26 '15 at 03:22
@ThomasRuddell - You need to go back and edit your original question to include a list of what worksheets are considered analysis worksheets and what worksheets are considered data worksheets. Only with a full list can create one rule to determine them all. — , Apr 26 '15 at 04:20

Loop through the workbook but exclude some worksheets?

2 Answers2