Alrighty try the below code:

Option Explicit


Private Sub Workbook_SheetActivate(ByVal Sh As Object) ' on worksheet selected

Dim ws As Worksheet    'declare variable ws as a worksheet
Dim LR As String    'declare variable LR as a string

Select Case ActiveSheet.Name    'if the activesheet's name
    Case "BHS"  'is BHS then (you can add more sheet names you want to include here
                'by adding , and the name in quotes after "BHS". Enter the names you do
                'not want to copy the value in H1
        
        'then do nothing
    
    Case Else 'if it's not those list then
           
        For Each ws In ThisWorkbook.Sheets   'for each worksheet in this workbook
            
            Select Case ws.Name   'if worksheet name
        
                Case Is = "BHS"  'is entitled one of the following sheet names then replace sheet names to skip with your sheet names
          
                      'Do nothing - These sheets are to be skipped over
          
                Case Else   'if the worksheet name is not one of the listed above
            
                        With ws
               
                            .Range("H1").Select     'select H1 in worksheet
                            LR = .Range("B6555").End(xlUp).Row      'sets variable LR equal to the last row that contains data in column B
                            Selection.Copy 'copy H1
                            .Range("H2:H" & LR).PasteSpecial    'paste the formula all the way down column H
                        End With
          
            End Select  'end the select case
        
        Next ws     'move to the next worksheet

End Select  'end the first select case

End Sub 'exit the code
To insert the code follow the below steps

1. Open VBA (Alt+F11)
2. In the Project Explorer on the far left (if not there press Ctrl+R) double click on This
Workbook.
3. Paste the above code here. Anything that appears in green is a comment to help you
understand the code.
4. Exit out of VBA

The code is set to run whenever you select a worksheet.

Let me know if this works for you or if you have any questions.

Thanks!