CODEWITHSUNDEEP
excel
Alex
Alex

Reputation: 170

Enhance code to run on all sheets in a workbook

How can I change this code so it runs on all sheets of a workbook? It works well, just need it to run on all sheets. =)

Option Explicit
Option Compare Text

Sub HideColumns()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Data")
Dim MyCell As Range
Dim HideMe As Range

Application.ScreenUpdating = False
    For Each MyCell In ws.Range("A2:EA2")
        If MyCell <> "First Name" And MyCell <> "Age" And MyCell <> "Gender" Then
            If HideMe Is Nothing Then
                Set HideMe = MyCell
            Else
                Set HideMe = Union(HideMe, MyCell)
            End If
        End If
    Next MyCell

    If Not HideMe Is Nothing Then
        HideMe.EntireColumn.Hidden = True
    End If
Application.ScreenUpdating = True

End Sub

Upvotes: 1

Views: 24

Answers (1)

urdearboy
urdearboy

Reputation: 14580

Loop through sheets using For Each loop & reset HideMe to Nothing before moving to next sheet.

Option Explicit
Option Compare Text

Sub HideColumns()

Dim ws As Worksheet 'Change made here
Dim MyCell As Range
Dim HideMe As Range

Application.ScreenUpdating = False
For Each ws in Worksheets 'and here
    For Each MyCell In ws.Range("A2:EA2")
        If MyCell <> "First Name" And MyCell <> "Age" And MyCell <> "Gender" Then
            If HideMe Is Nothing Then
                Set HideMe = MyCell
            Else
                Set HideMe = Union(HideMe, MyCell)
            End If
        End If
    Next MyCell

    If Not HideMe Is Nothing Then
        HideMe.EntireColumn.Hidden = True
    End If

Set HideMe = Nothing 'and here
Next ws 'and here

Application.ScreenUpdating = True

End Sub

Upvotes: 1

Related Questions