Run macro on multiple sheets, with exclusions

I have a workbook full of sheets that are using vlookup to reference data in another sheet.

I have a macro that successfully copies and pastes a column of data, ready for the sheet to start referencing new data when it's loaded into the "Data" sheet.

I need to apply that macro to all sheets simultaneously, so that I don't have to manually go into 40-50 sheets and click a button each time.

However, those sheets don't have a constant name, they are updated. So I thought I'd go about it by applying the macro to all sheets that aren't called "x", "y", "z", etc.

I've created this, but it doesn't seem to work, and I really don't know what I'm doing here. Any help would be greatly appreciated!

Sub Update_emp_RC_data()

     Dim ws As Worksheet
        Application.ScreenUpdating = False
        For Each ws In ThisWorkbook.Worksheets
            
            Select Case ws.Name
                Case "Instructions", "NEW STAFF", "Data", "Operational KPI", "Time Segment", "Clinical KPI", "AAB", "Branch", "Team List"
                    'If it's one of these sheets, do nothing
                Case Else
                    'Otherwise, update staff data
        Columns("E:E").Select
        Selection.EntireColumn.Hidden = False
        Columns("E:E").Select
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        Columns("F:F").Select
        Selection.Copy
        Columns("E:E").Select
        ActiveSheet.Paste
        Columns("E:E").Select
        Application.CutCopyMode = False
        Selection.Copy
        Columns("F:F").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("F2").Select
        Application.CutCopyMode = False
        Columns("E:E").Select
        Selection.EntireColumn.Hidden = True
                    
    
            End Select
        Next ws
        
        Application.ScreenUpdating = True

End Sub

Solution

I’m trying to understand the purpose of your script. It seems you aim to insert a column next to column E, copy the values from column E into the newly inserted column, and then hide column E. If that’s correct, the script can be simplified as follows:

All Range references should be explicitly qualified with their parent worksheet object.

Sub Update_emp_RC_data()
    Dim ws As Worksheet, r As Range
    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
        Case "Instructions", "NEW STAFF", "Data", "Operational KPI", "Time Segment", "Clinical KPI", "AAB", "Branch", "Team List"
            'If it's one of these sheets, do nothing
        Case Else
            'Otherwise, update staff data
            ws.Columns("F:F").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
            Set r = Application.Intersect(ws.Columns("E:E"), ws.UsedRange)
            If Not r Is Nothing Then r.Offset(0, 1).Value = r.Value
            Set r = Nothing
            With ws.Range("E:E")
                ' Option 1
                .Hidden = False
                ' same width as Col E
                ws.Range("F:F").ColumnWidth = .ColumnWidth
                .Hidden = True
                ' Option 2: change to a specific value (doesn't need to show Col E)
                ' ws.Range("F:F").ColumnWidth = 35
            End With
        End Select
    Next ws
    Application.ScreenUpdating = True
End Sub

Microsoft documentation:

Range.Offset property (Excel)

btw, Select is not needed in the script. How to avoid using Select in Excel VBA