Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Unhide Specific Worksheets Macro

  1. #1
    Join Date
    4th March 2006
    Posts
    209

    Unhide Specific Worksheets Macro

    I'm trying to unhide several sheets (then hide them again at the end of the macro using = False). When I run this macro nothing happens. What's wrong with the code?

    VB:
    For Each ws In Worksheets 
        If ws.Name = ("OP KPI 1 pivot") And ws.Name = ("OP KPI 2 pivot") Then 
            With ws 
                .Visible = True 
            End With 
        End If 
    Next ws 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th March 2007
    Location
    LONDON, UK
    Posts
    1,377

    Re: Unhide Specific Worksheets Macro

    It's probably this line of code

    VB:
    If ws. Name = ("OP KPI 1  pivot") And ws.Name = ("OP KPI 2 pivot") Then 
    
    
    Change the AND to OR

    VB:
    If ws. Name = ("OP KPI 1  pivot") Or ws.Name = ("OP KPI 2 pivot") Then 
    
    
    HTH

    Craig

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: Unhide Specific Worksheets Macro

    VB:
    Sub Show_Hide_Sheets() 
        Dim ws As Worksheet 
         
        For Each ws In Worksheets 
            If ws.Name = "Sheet1" Or ws.Name = "Sheet2" Then 
                ws.Visible = Not ws.Visible 
            End If 
        Next ws 
    End Sub 
    
    
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  4. #4
    Join Date
    4th March 2006
    Posts
    209

    Re: Unhide Specific Worksheets Macro

    Thanks for getting back to me so quickly. But if I try with all the sheets, I get a syntax error, and I can't figure out what's wrong:

    VB:
    For Each ws In Worksheets 
        If ws.Name = ("OP KPI 1 pivot") Or ws.Name = ("OP KPI 2 pivot") Or ws.Name = ("OP KPI 3 pivot") Or ws.Name = ("OP KPI 4 pivot"), _ 
        Or ws.Name = ("OP KPI 5 pivot") Or ws.Name = ("SC KPI 1 pivot") Or ws.Name = ("SC KPI 2 pivot") Or ws.Name = ("SC KPI 3 pivot"), _ 
        Or ws.Name = ("SC KPI 4 pivot") Then 
            With ws 
                .Visible = True 
            End With 
        End If 
    Next ws 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    Re: Unhide Specific Worksheets Macro

    A Select Case Statement would the best.

    Why not just show ALL sheets and hide the ones to remain remain hidden, if any?

    Or, use something like;
    VB:
    ws.Visible=ws.Name Like "*pivot" 
    
    
    Your code fails due the commas before the line continuation characters.

  6. #6
    Join Date
    4th March 2006
    Posts
    209

    Re: Unhide Specific Worksheets Macro

    You got a point, Dave. I'll try that. Thanks.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 9
    Last Post: July 19th, 2011, 00:51
  2. Unhide worksheets if they contain a certain string in a cell
    By the_crooked_toe in forum EXCEL HELP
    Replies: 2
    Last Post: June 1st, 2006, 21:31
  3. Hide unhide Macro
    By Pager in forum EXCEL HELP
    Replies: 2
    Last Post: April 11th, 2006, 03:15
  4. VBA : Excel VBA to Unhide All Worksheets at Once
    By imported_Anonymous in forum OPEN SOURCE: Hey! That is Cool!
    Replies: 0
    Last Post: March 12th, 2004, 07:12
  5. [Solved] VBA: Password to unhide worksheets
    By thuynh in forum EXCEL HELP
    Replies: 6
    Last Post: July 25th, 2003, 04:01

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
  •  
porno