Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Unhide Specific Worksheets Macro

  1. #1
    Join Date
    4th March 2006
    Usergroup
    Registered Users
    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
    Usergroup
    OzMVP
    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
    Usergroup
    Super Moderators
    Posts
    9,205

    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
    Usergroup
    Registered Users
    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
    Usergroup
    Administrators
    Posts
    31,875

    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
    Usergroup
    Registered Users
    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 General
    Replies: 2
    Last Post: June 1st, 2006, 21:31
  3. Hide unhide Macro
    By Pager in forum Excel General
    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 General
    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