How to run a macro on multiple sheets with one botton

Question

I have created the code below and applied it on one sheet and it worked well, now I created many sheets in the same workbook and want the same code on sheet one to be run on other sheets as soon as I click the botton in Sheet one. so I created the below code but it does not work on other sheets when I click the botton in Sheet1.

Private Sub CommandButton1_Click()
On Error Resume Next
Dim cng As Range, rng As Range
Application.EnableEvents = False
Dim WS_Count As Integer
Dim I As Integer
  WS_Count = ActiveWorkbook.Worksheets.Count
  For I = 1 To WS_Count
  Set rng = Range("C14:AG14")
  For Each cng In rng
  If Cells(14, cng.Column) = "HO" Or Cells(14, cng.Column) = "ho" Or Cells(14, cng.Column) = "Ho" Then
    Cells(15, cng.Column) = 8
    Cells(14, cng.Column) = ""
    Else
    Cells(20, cng.Column) = ""
  End If
Next cng
Next I
Application.EnableEvents = True
End Sub

Answer 1

As stated remove the On error line it is not needed and you want the errors in most cases to let you know where the code is wrong.

Also you need to set the sheet to the ranges as it iterate through the sheets, otherwise excel will use the active sheet:

Private Sub CommandButton1_Click()
Application.EnableEvents = False

Dim cng As Range, rng As Range
Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets
    With WS
        Set rng = .Range("C14:AG14")
        For Each cng In rng
            If .Cells(14, cng.Column) = "HO" Or .Cells(14, cng.Column) = "ho" Or .Cells(14, cng.Column) = "Ho" Then
                .Cells(15, cng.Column) = 8
                .Cells(14, cng.Column) = ""
            Else
                .Cells(20, cng.Column) = ""
            End If
        Next cng
    End With
Next WS
Application.EnableEvents = True
End Sub