How to automatically hide columns in other sheets

I would like to write a VBA function such that when a column in a "Master" sheet is hidden, the same column (of the same name) in all other sheets is also hidden. Below is what I have in my "Master" sheet code:

Private Sub Worksheet_Change(ByVal Target As Range)    
    Dim i As Integer, ws As Worksheet

    If IsEntireColumn(Target) = True Then    
        If Target.Hidden = True Then

            For i = 1 To Target.Columns.Count
                For Each ws In ThisWorkbook.Worksheets
                    If ws.Name = "Master" Or ws.Name = "Affiliate Codes" Then
                    Else
                        ws.Cells(1, ColumnIndexReturn(ws.Name, Target.Cells(1, i), 3)).EntireColumn.Hidden = True
                    End If
                Next ws
            Next i 

        End If    
    End If    

End Sub

For context, IsEntireColumn is a boolean function that returns True if the selected range is indeed entire columns, while ColumnIndexReturn is another function that returns the column index of a column by its name.

Since this is a Worksheet_Change sub, I would expect that each time I hide a column, the corresponding columns in other sheets will also be hidden automatically. However, so far this has not been happening, the columns in the other worksheets are not hiding themselves automatically.

May I know what may be the issue here? Feel free to let me know if any further information is required. Any help would be greatly appreciated! Thank you in advance!

Your request is a little bit tricky.

The main reason your code is not doing anything: The Change-event is only triggered if content of cells are changed. Showing/hiding a cell (or any other formatting) will not trigger the change-event.

A better event would the the Selection_Change-event that is triggered, whenever a cell is selected. However, this is also not working: This event is triggered when you select a column, it is triggered another time when you right click (to select the "Hide"-command), but it is not triggered after the column was hidden.

Best idea I came up is to "remember" a selected column in the Selection_Change event. When the Selection_Change is called the next time, do the show/hide logic for the previous remembered columns - sooner or later the user will select another cell. With one exception: When a user selects a different sheet immediately after hiding a column. To address this, we can use the Worksheet_Deactivate-event.

I came up with the following piece of code:

Option Explicit
Dim lastSelectedColumn As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not lastSelectedColumn Is Nothing Then
        ShowHideColumns lastSelectedColumn
    End If
    
    If IsEntireColumn(Target) Then
        Set lastSelectedColumn = Target
    Else
        Set lastSelectedColumn = Nothing
    End If
End Sub

Private Sub Worksheet_Deactivate()
    If Not lastSelectedColumn Is Nothing Then
        ShowHideColumns lastSelectedColumn
        Set lastSelectedColumn = Nothing
    End If
End Sub

Sub ShowHideColumns(Target As Range)
    Dim col As Long, ws As Worksheet
    For col = 1 To Target.Columns.Count
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> "Master" And ws.Name <> "Affiliate Codes" And ws.Name <> Target.Parent.Name Then
                Dim cell As Range
                Set cell = Target.Cells(1, col)
                ws.Cells(1, cell.Column).EntireColumn.Hidden = cell.EntireColumn.Hidden
            End If
        Next ws
    Next col
End Sub

Function IsEntireColumn(Target As Range) As Boolean
    IsEntireColumn = (Target.Rows.Count = Target.Parent.Rows.Count)
End Function

Note that the code not only hides the column(s), but it will also show them again.

@FunThomas, you were 24 minutes quicker

Place this code in module "ThisWorkbook"

Option Explicit

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim i As Integer, c As Integer, ws As Worksheet
    For i = 1 To Worksheets("Master").UsedRange.Columns.Count
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> "Master" And ws.Name <> "Affiliate Codes" Then
               c = ColumnIndexReturn(ws.Name, Worksheets("Master").Cells(1, i))
               If c <> 0 Then
                  ws.Cells(1, c).EntireColumn.Hidden = Worksheets("Master").Columns(i).EntireColumn.Hidden
               End If
            End If
        Next
    Next
End Sub
Private Function ColumnIndexReturn(sSheetName As String, sColumnName As String) As Integer
    Dim i As Integer
    For i = 1 To Worksheets(sSheetName).UsedRange.Columns.Count
        If Worksheets(sSheetName).Cells(1, i).Value = sColumnName Then
           ColumnIndexReturn = i
           Exit Function
        End If
    Next
End Function

This code only runs when the user changes the sheet, not on every cell change, also it shows a column again when it is changed to visible in in Master

Here is a more versatile version of hennep code.

Sub SyncMasterColumnVisibility(Optional TargetWorksheet As Worksheet)
Application.ScreenUpdating = False
Const MasterSheetName As String = "Master"
Dim Excludes As Variant
Excludes = Array("Master", "Affiliate Codes")
Dim TargetWorksheets As New Collection
Dim ws As Worksheet
If Not TargetWorksheet Is Nothing Then
TargetWorksheets.Add TargetWorksheet
Else
For Each ws In ThisWorkbook.Worksheets
On Error Resume Next ' In case Match function does not find a match
If IsError(Application.WorksheetFunction.Match(ws.Name, Excludes, 0)) Then
TargetWorksheets.Add ws
End If
On Error GoTo 0 ' Reset error handling
Next ws
End If
Dim MasterSheetColumnVisibility As New Collection
Dim Cell As Range
For Each Cell In ThisWorkbook.Worksheets(MasterSheetName).UsedRange.Rows(1).Cells
If Len(Cell.Value) > 0 Then
On Error Resume Next
MasterSheetColumnVisibility.Add Cell.EntireColumn.Hidden, CStr(Cell.Value)
On Error GoTo 0
End If
Next
Dim Hidden As Boolean
Dim tWs As Variant ' Use variant type because the collection may contain different object types
For Each tWs In TargetWorksheets
For Each Cell In tWs.UsedRange.Rows(1).Cells
On Error Resume Next ' In case Match function does not find a match
Hidden = MasterSheetColumnVisibility(CStr(Cell.Value))
If Err.Number = 0 Then Cell.EntireColumn.Hidden = Hidden
On Error GoTo 0 ' Reset error handling
Next
Next tWs
End Sub

Using the Workbook_SheetActivate event:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
SyncMasterColumnVisibility Sh
End Sub

Using the Workbook_SheetDeactivate event:

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Const MasterSheetName As String = "Master"
If Sh.Name = MasterSheetName Then SyncMasterColumnVisibility
End Sub