Results 1 to 3 of 3

Grouping Sheets using VBA to auto hide

Threaded View

  1. #1
    Registered User
    Join Date
    08-11-2011
    Location
    Northampton, England
    MS-Off Ver
    Excel 2003
    Posts
    15

    Grouping Sheets using VBA to auto hide

    I am trying to make a workbook that will group diffrent types of sheets such as P&L's, Site information and Vehicles. Excel file enclosed.

    I have used this code which works:

    Private Sub Worksheet_Activate()
    Dim sh As Worksheet 'Object
    Dim arr As Variant

    arr = Array("Facility 1 P&L", "Facility 2 P&L", "Facility 3 P&L") '

    On Error GoTo XIT
    With Application
    .EnableEvents = False
    .ScreenUpdating = False
    End With

    For Each sh In ThisWorkbook.Sheets
    If Not sh.Name Like "Master*" Then
    sh.Visible = xlSheetHidden
    End If
    Next sh

    For Each sh In Sheets(arr)
    sh.Visible = xlSheetVisible
    Next sh

    XIT:
    With Application
    .EnableEvents = True
    .ScreenUpdating = True
    End With
    End Sub



    Private Sub Worksheet_Deactivate()
    Dim sh As Worksheet
    Dim arr As Variant

    arr = Array("Facility 1 P&L", "Facility 2 P&L", "Facility 3 P&L") '

    On Error GoTo XIT
    With Application
    .EnableEvents = False
    .ScreenUpdating = False
    End With

    For Each sh In Sheets(arr)
    sh.Visible = xlSheetVisible
    Next sh

    XIT:
    With Application
    .EnableEvents = True
    .ScreenUpdating = True
    End With
    End Sub


    Now the problem that I have is although I can rename the array sheets and get them to hide, the sheets Master1, Master2 and Master3 would be better named P&L, Site Info and Vehicles.

    I assume this function controls the display of the master sheets so they are always on view:

    For Each sh In ThisWorkbook.Sheets
    If Not sh.Name Like "Master*" Then
    sh.Visible = xlSheetHidden
    End If
    Next sh

    Is there a way to change the master* to use the three above. Every time I try and change one the sheet disappears.

    Thanks
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1