For each Worksheet in Worksheets not working

Question

I'm rather new to VBA coding, so excuse my ignorance, as I'm probably doing something super simple here wrong. I've written a small VBA script that aligns data for a worksheet, using a master list of names as a reference point.

It works fine on one worksheet at a time, but when entered into a "For each worksheet in worksheets" loop, it applies it to the first worksheet and then stops. I've debugged it, and the "for each" part is working, but the code isn't being applied past the first worksheet. Any tips?

I've tried moving the Dim both inside and outside of the "for each" variable, and it seems to make no difference. All references to the worksheet in the code are the variable that changes with each step, from my understanding.

I've made edits to the code as suggested, and it still seems to only apply to the first worksheet:

Option Explicit

Sub AlignAll()
Dim Current As Worksheet
Dim n As Long, x As Long, i As Long, a As Range, c As Range
Set c = Worksheets("Masterlist").Range("A6:A200")

For i = 1 To ThisWorkbook.Worksheets.Count
    Set Current = ThisWorkbook.Sheets(i)
        If Current.Name <> "Yearly" And Current.Name <> "Masterlist" Then 'Prevent Conflicts
            n = Cells.SpecialCells(11).Row
            'sets a as the range of the current worksheet and c as range of the master list
            Set a = Current.Range("A6:A200")
            a(n + 1) = Chr(255): c(n + 1) = Chr(255)
            a.Sort a(1), 1, Header:=xlNo
            c.Sort c(1), 1, Header:=xlNo
            Do
            x = x + 1 'steps through each range
            If a(x) > c(x) Then 'adds rows as needed to align data
                a(x).EntireRow.Insert xlShiftDown
            End If
            If x > 10 ^ 4 Then Exit Do
            Loop Until a(x) = Chr(255) And c(x) = Chr(255)
            a(x).ClearContents: c(x).ClearContents 'resets variables
        End If
Next i
End Sub

Answer 1

Other simple workaround:

Option Explicit

Sub AlignAll()
Dim Current As Worksheet
Dim Current As Worksheet
Dim n As Long, x As Long, i as Long '
Dim a As Range

on error resume next 'really needed ? might solve problems to (temporarely) remove it , to debug...

For i=1 to ActiveWorkbook.Worksheets.count
   set Current = ActiveWorkbook.Sheets(i)
   'Do Stuff
Next i
End Sub

Answer 2

Here are some additional tweaks that might help, denoted with the '<~ comment:

Option Explicit
Sub AlignAll()

Dim Current As Worksheet
Dim n As Long, x As Long
Dim a As Range, c As Range

'set static references up front
Set c = Worksheets("Masterlist").Range("A6:A200")

For Each Current In ThisWorkbook.Worksheets
    If Current.Name <> "Yearly" And Current.Name <> "Masterlist" Then '<~ skip master and yearly
        n = Current.Cells.SpecialCells(11).Row '<~ include sheet context
        'sets a as the range of the current worksheet
        Set a = Current.Range("A6:A200")
        a(n + 1) = Chr(255): c(n + 1) = Chr(255)
        a.Sort a(1), 1, Header:=xlNo
        c.Sort c(1), 1, Header:=xlNo
        Do
        x = x + 1 'steps through each range
        If a(x) > c(x) Then 'adds rows as needed to align data
            a(x).EntireRow.Insert xlShiftDown
        End If
        If x > 10000 Then Exit Do
        Loop Until a(x) = Chr(255) And c(x) = Chr(255)
        If x > 10000 Then MsgBox ("Do Loop possibly had an error, reached 10000!") '<~ warning for 10k loop
        a(x).ClearContents: c(x).ClearContents 'resets variables
    End If
Next Current

End Sub