CODEWITHSUNDEEP
vbaexcel
Mech
Mech

Reputation: 101

VBA Macro in Excel for hiding all worksheets that are not selected

I've been using the following VBA macro code below to hide all but the active worksheet:

    Sub HideWorksheets()
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> ThisWorkbook.ActiveSheet.Name Then
            ws.Visible = xlSheetHidden
        End If
    Next ws
End Sub

Are there any options to extend it so that it will hide all but the selected worksheets?

Upvotes: 1

Views: 102

Answers (1)

PatricK
PatricK

Reputation: 6433

You need to access the Windows(#).SelectedSheets. One way is to hide all except ActiveSheet, then unhide those Selected.

Option Explicit

Sub HideWorksheets()
    Dim ws As Worksheet

    Application.ScreenUpdating = False
    ' Hide all except activeone
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> ThisWorkbook.ActiveSheet.Name Then ws.Visible = xlSheetHidden
    Next
    ' Unhide selected worksheets
    For Each ws In ThisWorkbook.Windows(1).SelectedSheets
        ws.Visible = xlSheetVisible
    Next ws
    Application.ScreenUpdating = True
End Sub

Upvotes: 2

Related Questions