Getting My Script to run on all sheets (Excel)

Question

This code works on one sheet , and now I'm trying to get it to work on several sheets, avoiding the first two sheets ("AA" and "Word Frequency") .

Source code here (see @Jeeped answer)

Link to worksheet here

I tried to find the code of the associated streams, which I found (link 1 , 2 ) However, I do not know how (and if necessary) to use objects Ws.Nameand Ws.Rangeto my existing code.

It seems that the code activates Sheet1 with With Worksheets("Sheet1"), and I tried to replace it with the following method:

• Create a ForbyGroupCounter () loop function to determine the number of worksheets and run all existing sheets. Each worksheet will grow with the variable "i"

• For loop in byGroupCounter () calls the function byGroup (i) to run the source code on the selected sheet (for example, sheet "i")

• The byGroup () function starts the process on sheet i.

• The part where I believe that I am getting the error message: Replacing the code With Worksheets("Sheet1")with With Ws, where Ws = Worksheets(Sheet_Index)and Sheet_Index is equal to i, determined frombyGroupCounter()

I believe that I need to add a prefix Wsbefore .Range, but all that I tried, I continue to receive the error message "Unable to execute code in break mode."

Current Code:

Sub byGroupCounter()

  Dim i As Integer

  Application.ScreenUpdating = False

For i = ActiveSheet.Index To Sheets.Count
  byGroup i
Next i
Application.ScreenUpdating = True
End Sub

Sub byGroup(ByVal Sheets_Index As Integer)
  Dim g As Long, s As Long, aSTRs As Variant, aGRPs As Variant
  Dim Ws As Worksheet
  Set Ws = Worksheets(Sheet_Index)

appTGGL bTGGL:=False

' I believe the next line is where I am doing something wrong:
With Ws
    aSTRs = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).Value2
    With .Range(.Cells(1, 5), .Cells(Rows.Count, 1).End(xlUp).Offset(0,        Application.Match("zzz", .Rows(1)) - 1))
        .Resize(.Rows.Count, .Columns.Count).Offset(1, 0).ClearContents
        aGRPs = Ws.Cells.Value2
    End With

    For s = LBound(aSTRs, 1) To UBound(aSTRs, 1)
        For g = LBound(aGRPs, 2) To UBound(aGRPs, 2)
            If CBool(InStr(1, aSTRs(s, 1), aGRPs(1, g), vbTextCompare)) Then
                aGRPs(s + 1, g) = aSTRs(s, 1)
                Exit For
            End If
        Next g
    Next s

    .Cells(1, 5).Resize(UBound(aGRPs, 1), UBound(aGRPs, 2)) = aGRPs

  End With

  appTGGL
End Sub

Public Sub appTGGL(Optional bTGGL As Boolean = True)
    Debug.Print Timer
    Application.ScreenUpdating = bTGGL
    Application.EnableEvents = bTGGL
    Application.DisplayAlerts = bTGGL
    Application.Calculation = IIf(bTGGL, xlCalculationAutomatic,     xlCalculationManual)
End Sub

Answer 1

6

< < <

Sub byGroup()
    Dim g As Long, s As Long, aSTRs As Variant, aGRPs As Variant, sh As Worksheet    '<<<

    appTGGL bTGGL:=False
    For Each sh In Sheets    '<<<
        If sh.Name <> "AA" And sh.Name <> "Word Frequency" Then    '<<<<
            With sh    '<<<
                aSTRs = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).Value2
                With .Range(.Cells(1, 5), .Cells(Rows.Count, 1).End(xlUp).Offset(0, Application.Match("zzz", .Rows(1)) - 1))
                    .Resize(.Rows.Count, .Columns.Count).Offset(1, 0).ClearContents
                    aGRPs = .Cells.Value2
                End With

                For s = LBound(aSTRs, 1) To UBound(aSTRs, 1)
                    For g = LBound(aGRPs, 2) To UBound(aGRPs, 2)
                        If CBool(InStr(1, aSTRs(s, 1), aGRPs(1, g), vbTextCompare)) Then
                            aGRPs(s + 1, g) = aSTRs(s, 1)
                            Exit For
                        End If
                    Next g
                Next s

                .Cells(1, 5).Resize(UBound(aGRPs, 1), UBound(aGRPs, 2)) = aGRPs

            End With
        End If    '<<<<
    Next sh    '<<<
    appTGGL
End Sub

Public Sub appTGGL(Optional bTGGL As Boolean = True)
    Debug.Print Timer
    Application.ScreenUpdating = bTGGL
    Application.EnableEvents = bTGGL
    Application.DisplayAlerts = bTGGL
    Application.Calculation = IIf(bTGGL, xlCalculationAutomatic, xlCalculationManual)
End Sub