+ Reply to Thread
Results 1 to 4 of 4

Better way to unhide/hide multiple sheets

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    11-25-2012
    Location
    Edmonton, Alberta
    MS-Off Ver
    2010
    Posts
    146

    Better way to unhide/hide multiple sheets

    I have a macro that I want to unhide 10 sheets print based on a formula (as I need to unhide them before I can print them I believe) then hide them again

    right now I have the following line 10 times to unhide the sheets.

    Sheets("Don't Unhide Motor Report (1)").Visible = True
    Sheets("Don't Unhide Motor Report (2)").Visible = True
    etc to 10

    is there a better way to write this? or am I stuck with these 10 lines at start and then 10 "hide" lines at the end?
    Last edited by JBeaucaire; 02-28-2013 at 09:21 PM. Reason: Added code tags, as per forum rules. Don't forget!

  2. #2
    Valued Forum Contributor
    Join Date
    08-13-2012
    Location
    Gardony, Hungary
    MS-Off Ver
    Excel 2003
    Posts
    558

    Re: Better way to unhide/hide multiple sheets

    Here are some ideas.
    edit: corrected the code
    Sub YourMainMacro()
    
    Call SheetHider '(unhides the sheets)
    'here goes the code for printing or something else
    Call SheetHider '(hides the sheets)
    
    End Sub
    
    Sub SheetHider()
    
    Dim SheetsArray
    Dim i As Integer
    
    SheetsArray = Array("SheetName1", "SheetName2") 'etc
    For i = Lbound(SheetsArray) To UBound(SheetsArray)
        Sheets(SheetsArray(i)).Visible = Not Sheets(SheetsArray(i)).Visible
    Next i
    
    End Sub
    
    Sub SheethiderAlternative()
    
    'if all the worksheet names are similar as in your example
    'like this: Sheets("Don't Unhide Motor Report (1)")
    'then you can do it a little easier
    
    Dim i As Integer
    Dim str As String
    
    For i = 1 To 3
        str = "Don't Unhide Motor Report (" & i & ")"
        Sheets(str).Visible = Not Sheets(str).Visible
    Next i
    
    End Sub
    Last edited by RHCPgergo; 02-28-2013 at 06:21 PM. Reason: ooops 3x

  3. #3
    Forum Contributor
    Join Date
    11-25-2012
    Location
    Edmonton, Alberta
    MS-Off Ver
    2010
    Posts
    146

    Re: Better way to unhide/hide multiple sheets

    Thanks,
    I'll play around with it a bit when I get a chance and let you know how it works out

  4. #4
    Forum Contributor
    Join Date
    11-25-2012
    Location
    Edmonton, Alberta
    MS-Off Ver
    2010
    Posts
    146

    Re: Better way to unhide/hide multiple sheets

    I tweaked this for another set of pages, it works good, but if a page is visible it hides it, then I get it error.
    Is there any way that it can ignore sheets that are visible and then hide all the visble ones after it has deleted what needs to be deleted?


    Sub ClearDailys()
    '
    ' ClearDailys Macro
    '
    
    '
    
    Call SheetHider '(unhides the sheets)
    'here goes the code for printing or something else
    Dim i As Integer
    Dim str As String
    
    For i = 1 To 31
        str = "Daily (" & i & ")"
    Sheets(str).Select
        Range("B4:B11,D4:D8,D10,F3,B14:F38").Select
        Range("B14").Select
        Range("B4:B11,D4:D8,D10,F3,B14:F38,E41:L50,a41:a50").Select
        Range("E41").Activate
        Selection.ClearContents
        ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Next i
    Call SheetHider '(hides the sheets)
    
    End Sub
    Sub SheetHider()
    
    'if all the worksheet names are similar as in your example
    'like this: Sheets("Don't Unhide Motor Report (1)")
    'then you can do it a little easier
    
    Dim i As Integer
    Dim str As String
    
    For i = 1 To 31
        str = "Daily (" & i & ")"
        Sheets(str).Visible = Not Sheets(str).Visible
    Next i
    
    End Sub
    Last edited by groundin; 02-28-2013 at 11:59 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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