27 February 2010

Unhide multiple sheets in Excel

Microsoft Excel has hundreds of functions and commands, but even so there are some things the Microsoft engineers failed to include. One of my recurrent annoyances is the lack of a command to unhide more than one sheet at a time. Hiding several sheets at once is very easy: you just select them by holding down the Ctrl key and then hide the group from the Format menu. The unhide option on the other hand presents you with a list of hidden sheets where you can only select one item to display it again.

When working with large files and lots of sheets, I prefer to hide the sheets where data is stored or where intermediary calculations are performed, and leave only the final results and charts visible to improve the readability of the file. But Excel makes it hard to bring back these sheets if you want to edit them again.

The only solution I have found to this problem is to create a VBA macro that runs through the list of sheets and toggles their status to visible. The code is very short, only a couple of lines:

Sub Unhide_all_sheets()    
' Unhide all sheets with one click
Dim sh As Worksheet
For Each sh In Sheets
sh.Visible = xlSheetVisible
Next
End Sub

You can add this code to your file by opening the Visual Basic Editor (for example with the keyboard shortcut Alt+F11) and pasting the code in a new module. If you also protect your hidden sheets, you can add the line sh.Unprotect in the For Each… Next loop to easily unprotect all the sheets in the file.

To integrate the new function in Excel, the most efficient solution is to create a new menu entry (for older Excel versions, including 2003) or a new button and assign this macro to it. And if you want the macro to be available in all the new files, you should add it directly in the Excel default template, following the instructions in this article.

Update: As someone mentioned in the comments, the code here throws a Type mismatch error in Excel 2010, likely caused by some changes to VBA for this version. Fortunately it's easy enough to fix, just remove line 3 from the code, Dim sh As Worksheet. This allows Excel to guess on its own the type of variable it needs. The change has propagated to Excel 2013 as well, so if you are using the Preview you also need to discard that code line for the macro to work. And, before you ask, no, Excel 2013 doesn't have an easier way to unhide several sheets simultaneously...

88 comments:

  1. I performed a Google search for unhiding multiple Excel sheets, where I came across your blog and the VBA solution which works perfectly. Thanks!

    ReplyDelete
  2. You rock the free world! This works beautifully. Thank you so much.

    ReplyDelete
  3. Google search worked first try. Your macro is perfect. Peter

    ReplyDelete
  4. Thank you so much for posting this! I have a workbook with 110+ sheets and thought there had to be a way, even for someone with no VB at all. You've saved me a lot of time and hair-tearing.

    ReplyDelete
    Replies
    1. Wow, I can imagine what a pain it would be to do this manually for hundreds of sheets! Glad to help!

      Delete
  5. Oh this is just Beautiful..!

    ReplyDelete
  6. You are the best!!!! I love macros.

    ReplyDelete
  7. I just got back at least an hour of my life. Thanks!

    ReplyDelete
  8. This has oppened up a world of opportunities. Kind of makes me wonder what else I could be doing with Macros.
    Thanks!

    ReplyDelete
  9. First macro "written"/run ever! I felt so successful. Thank you for the quick and easy instructions.
    Like SUNY, I'm curious about other things I can figure out now.

    ReplyDelete
    Replies
    1. Oh, there are many things you can automate with macros in Excel, and in Word as well. You just need to experiment and search for more information on the web, there is plenty available.

      Delete
  10. Wow....worked really well

    ReplyDelete
  11. This works great

    ReplyDelete
  12. Thanks so much for this! This worked great!

    ReplyDelete
  13. Hi thanks for this. How does one do the reverse? i.e. Automatically hide all sheets that are currently open? Thanks!

    ReplyDelete
    Replies
    1. That's much easier: right-click a sheet, choose "Select all sheets", right click again and choose "Hide". But be aware that you cannot hide all the sheets, you need to have at least one visible per worksheet.

      Delete
  14. thank you works perfectibility

    ReplyDelete
  15. Tom pickering email: Tom.pickering@ssfb.com10 July, 2012 16:48

    Hey i was wondering if you could help me further? i have a problem when i consolidate my work sheets together it has the tendency to hide multiple rows..(in the past its only been 10-30 so i have done it manually) i have little script knowledge but good with tech! but cannot seen to change your code to unhidden multiple rows? this is what i have right now

    Sub Unhide_all_rows()
    ' Unhide all sheets with one click
    Dim sh As Worksheet
    For Each sh In Row <==== is currently the problem (I think )
    sh.Visible = xlRowVisible
    Next
    End Sub

    ReplyDelete
    Replies
    1. Unfortunately it doesn't work that way... The object Sheets that I used is internally defined in Excel to contain all sheets, so it won't work in connection to Row as you tried.
      If you want to un-hide rows you need to add this line:
      sh.Rows.Hidden = False
      instead of:
      sh.Visible = xlRowVisible
      to my original code.

      Delete
    2. Tom pickering email: Tom.pickering@ssfb.com11 July, 2012 18:06

      Thank you very much!

      I will test this out when i next needed it but apparently it was just a bug!?

      Ah well thanks!

      Tom

      Delete
  16. Really useful. Saved my day.

    ReplyDelete
  17. Oh Man, you will be thanked forever for this. Top stuff!

    ReplyDelete
  18. I have tried it and it works wonderfully. Thanks a ton!!!

    ReplyDelete
  19. Hi. Does this work for Excel 2010 as well? I'm getting an error message (Run-time error '13': Type mismatch). Any thoughts?

    ReplyDelete
    Replies
    1. You're right, I get the same error in Excel 2010. Fortunately it's easy enough to fix, just remove line 3 [Dim sh As Worksheet]. This allows Excel to guess on its own the type of variable it needs, hence circumventing the type mismatch.
      Must check how Excel 2013 is handling this...

      Delete
  20. I'm getting an error that reads...Compile Error Ambiquous Name detected Unhide_All_Sheets

    ReplyDelete
  21. Thank you so much! It worked perfectly when I took off the third line.

    ReplyDelete
  22. pls how can u unhide some selected sheets, i tried this code and am getting a debug error
    Sub Sheets_Unhide()

    Dim sh As Worksheet
    Dim myPassword As String
    myPassword = "password"
    If InputBox("Please enter password to show sheets: ") = myPassword Then
    Application.ScreenUpdating = False
    For Each sh In Sheets(Array("Sheet1", "Sheet2"))
    sh.Visible = True
    Next sh
    Application.ScreenUpdating = True
    Else
    MsgBox ("Incorrect password, macro stopping")
    End If
    End Sub

    ReplyDelete
    Replies
    1. This code is working fine for me in Excel 2013 Preview. What kind of error are you getting exactly? The debugger should jump to the line with the error.

      Delete
    2. Gees must have been tooo exhausted yesturday, its working now, thanks a zillion boss. a quick one, i want a vba code to check whether the first password i entered in the same as the confirmation i tried
      if textbox3.value = textbox2.value then

      unload me

      else

      msgbox " password doesnt match" vbcritical

      end if


      but its not working, pls assist

      Delete
  23. Thank you. Worked perfectly without any changes.

    ReplyDelete
  24. thanks a ton, macro worked perfectly....saved lots of time

    ReplyDelete
  25. This comment has been removed by the author.

    ReplyDelete
  26. Add this macro to your Personal Macro Workbook and you will have it available for all workbooks you use!!
    Link below shows you how to add to you Personal Macro Workbook
    http://office.microsoft.com/en-us/excel-help/save-my-macro-in-the-personal-workbook-RZ102337714.aspx?CTT=1&client=1&section=4

    ReplyDelete
  27. Great! Worked well first time. Thanks.

    ReplyDelete
  28. Works perfectly fine. Thank you.

    ReplyDelete
  29. Works perfectly fine. Thank you....

    ReplyDelete
  30. Thank you so much, this is what I've been looking for!

    BUT.... is there a way upon hitting the unhide button for a password prompt? In other words, I want only Sales to see my first page and estimating, etc. to be able to access and manipulate all of the other spreadsheets that contain formulas, etc.



    THANKS,
    Steve

    ReplyDelete
    Replies
    1. Of course, that should be possible as well, you can see some examples here: http://www.vbaexpress.com/forum/showthread.php?1854-Solved-password-before-running-macro
      and here: http://www.mrexcel.com/archive/VBA/21436.html.
      I haven't tested them, but as far as I can tell they should work.
      Alternatively you can enable password protection on the sheets you will hide, but that doesn't prevent others from un-hiding them, only from changing formulas and data.

      Delete
    2. George,
      MANY Thanks for the response. I'll try those examples you've cited.

      BUT another thing I noticed for my application is that if someone goes in (with the password) and manipulates the sheets with formulas, that's fine but then if he or she does NOT hide these pages, then the person opening the workbook is not required to use a password and ALL pages are visible! I can't have Sales or the client seeing these pages, only the Summary page for them.

      So I hope there's some kind of prompt or something that reminds the estimator to hide the files before saving. Hope this makes sense.

      ANYONE??



      Thanks again, George, I can't tell you how happy I am to have found your site. My boss will think I'm brilliant :)



      Steve

      Delete
  31. Actually, for my application, I just used Respuzy's code above and it works!

    However, I noticed that if I "forget" to HIDE my pricing sheets after my revisions, the workbook will open again WITH my "hidden" sheets showing and I certainly don't want these seen by my Sales dept. or the client themselves! In other words, is there something I can do to automatically hide the sheets I want hidden automatically upon saving the document??


    Thanks again,
    Steve

    ReplyDelete
    Replies
    1. Yes, that's normal, if somebody saves and the sheets are visible, they will be visible the next time someone opens the file.
      To prevent that, you can add another piece of code that will hide those sheets before saving, using the Excel BeforeSave Event. It should look like this:

      Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel as Boolean)
      Sheets("exact sheet name").Hide ' repeat this for each sheet you need to hide
      Cancel = False ' oddly enough this will save the file
      End Sub

      As an alternative to the first question (preventing people from unhiding sheets) you can also protect the entire workbook with a password, that prevents others users from making changes to the structure (like adding, deleting, unhiding sheets).

      Delete
    2. Hi George,

      MANY thanks for the reply - I was fearing that there wouldn't be a solution...

      But, being the terrible novice, I am, this is not working and it's probably because I'm not entering it into the correct place.

      I'm using the great code above that Respuzy posted on 30 October, 2012 20:47:

      Sub Sheets_Unhide()

      Dim sh As Worksheet
      Dim myPassword As String
      myPassword = "password"
      If InputBox("Please enter password to show sheets: ") = myPassword Then
      Application.ScreenUpdating = False
      For Each sh In Sheets(Array("Sheet1", "Sheet2"))
      sh.Visible = True
      Next sh
      Application.ScreenUpdating = True
      Else
      MsgBox ("Incorrect password, macro stopping")
      End If
      End Sub

      This is great because it allows me a password to UNHIDE the sheets which is exactly what I want to expose the pricing sheets for the estimators. So my hide and unhide are working, I just need to get your code integrated correctly so if we forget to HIDE before we send the file to Sales, the hidden sheets won't be exposed when Sales opens the Excel file....

      Can't thank you enough, George, for your help and patience with a simpleton like me! :)


      Best,
      Steve

      Delete
    3. One last thing...

      This is the code I'm using to hide my estimating sheets only - so do I use your code under THIS code or my UNHIDE code:

      Public Sub HideSheets()
      Dim SheetName As Variant
      For Each SheetName In Array("Est1", "Est2", "Est3", "Est4")
      Worksheets(SheetName).Visible = xlSheetHidden
      Next SheetName
      End Sub


      Thank you so very much,
      Steve

      Delete
    4. Hi,

      As I mentioned before, you need to use a special Excel method to insure the code is run every time the workbook is saved. You don't need to create a separate Sub (like you did with HideSheets), instead in the Visual Basic window right-click the workbook name, click Show code and insert it there. It should be something like this:

      Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel as Boolean)
      ' you code for hiding the sensitive sheets here:
      Dim SheetName As Variant
      For Each SheetName In Array("Est1", "Est2", "Est3", "Est4")
      Worksheets(SheetName).Visible = xlSheetHidden
      Next SheetName
      Cancel = False ' this will save the file
      End Sub

      I have just tested this in Excel 2013 and it works for me. Let me know if you get this working!

      Delete
  32. George,

    GOD BLESS YOU! You don't know how much you've helped me out!!

    Tremendous amount of thanks.


    Steve

    ReplyDelete
  33. George, this works and even my 2 IT guys didn't think it could be done.

    But I do want to ask you something - it's a different topic - and I can research it but maybe run it by you first to see if it is even possible.

    Is there button I can use that can change the value of multiple cells to the value, "zero." Each item priced has it's own tab and within each tab there is a summary of materials price, labor price, rental price and total sell cost and this total cost is transferred to the main page with the description. But if a certain item is an option and the client declines this option, I'd like a checkbox or similar that we can check if an option is NOT selected which would change all values to "zero" and thus change this total sell value on the first page also to zero. Then Production - and Accounting on the back end - would know that because there was a "zero" in that sale price reflected onto the front page, the client did not elect it. Otherwise the formulas stay the same - even if the checkbox accidently checked and then unchecked, ie, my formulas would stay intact.

    Is this possible?


    THANK YOU!


    Steve


    ReplyDelete
    Replies
    1. Hi Steve,
      I'm not sure I understand exactly what you need here...
      If I understand correctly, in theory you shouldn't need a checkbox and VBA code in Excel for something like this. If you have individual prices on tabs you can add an extra column with two possible values (Yes/No depending on what the client selected) and adjust formulas to take this into account for calculations on the first sheet.
      You can also lock these cells with conditional formatting so that people can only switch between Yes and No.

      Delete
  34. Hi George,

    Thank you again for the response and the invaluable info - I will investigate your suggestion about conditional formatting.

    All calculations are done within the individual tabs, so the only elements the first sheet (the proposal that will be sent to the client) are the itemized descriptions with their associated quantities and descriptions. Each item on the front page has its individual tab with all of the calculations and formulas that indicate how the specific price for this item has been arrived at - including a summary on each sheet, something like this:

    Total sell cost - $$$
    Total materials cost - $$$
    Total Labor costs - $$$
    Total vendor costs - $$$

    Often times, in my industry (trade show exhibits) designs are "over" designed, ie, designed over the client's budget so we need to make certain things an option, or, the designer will intentionally create variations of a design with optional elements.

    What I want to do is IDEALLY, AFTER a client has signed the proposal and selected certain options, I would have an "options" button on each page. When I or the other estimator checks this box, the caption might be, "If this is an option and client has NOT selected this," then all the values of the above pricing summary section would become zero. This way, when production sees the front page of this "revised" proposal, they would see $0 next to a certain option and they would know the client did NOT select this option. Also, Accounting would know as well.
    IF the client DOES select the option, I would do nothing, all pricing (with formulas) are still intact.

    I would want the "luxury" that if I or the other estimator inadvertantly click an options box for the wrong item, we could correct ourselves by clicking it again (or unchecking it) so the formulas and values would be left intact.

    So this is my challenge. Hope someone can steer me in the right direction!


    THANK YOU,
    Steve

    ReplyDelete
    Replies
    1. OK, in this case my idea would apply: set up a column with Yes/No as the only values and update the formulas for total costs with an IF function: if selection is no, the value would be $0, else use the formula you already have in place.
      Also, I meant 'Data validation' instead of 'Conditional formatting', for some reason I always mix those two up.

      Delete
    2. George - yes it is that simple! I have my Data Validation of just "yes, no" and have my IF statement referencing that cell that signifies whether that option has been selected (YES) or not (No).

      Can't thank you enough, George, for all of your gracious help. It has made my worry and headaches go away and I've learned something too.

      Again, thank you from the bottom of my heart for your help and patience!!!!



      Steve

      Delete
    3. I'm glad to help! :)
      Excel is not that bad (after you have years of experience working with it...)

      Delete
    4. Hi George,

      I feel like I'm a bad rash that won't go away!

      I just noticed that if I save a copy of my main pricing spreadsheet, go in and make changes to my password-protected hidden pages, then save the changes, very strangely the original spreadsheet it somehow automatically opened...

      Here's the code I'm using in my Module1:

      Sub Sheets_Unhide()

      Dim sh As Worksheet
      Dim myPassword As String
      myPassword = "obama"
      If InputBox("Please enter password to show sheets: ") = myPassword Then
      Application.ScreenUpdating = False
      For Each sh In Sheets(Array("Est1", "Est2", "Est3"))
      sh.Visible = True
      Next sh
      Application.ScreenUpdating = True
      Else
      MsgBox ("Incorrect password, macro stopping")
      End If
      End Sub

      In my code for 'This Workbook' I have:

      Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
      ' you code for hiding the sensitive sheets here:
      Dim SheetName As Variant
      For Each SheetName In Array("Est1", "Est2", "Est3")
      Worksheets(SheetName).Visible = xlSheetHidden
      Next SheetName
      Cancel = False ' this will save the file
      End Sub

      Is there something I have done wrong? I need to open up a new spreadsheet for each new Design or job...


      Thanks again so much, I feel like I'm a leech and I truly don't mean to be!!


      Best,
      Steve

      Delete
    5. Hmm, that is a curious problem indeed, I haven't encountered it personally...
      So if I understand correctly you have your main file (let's call it Main), make a copy of it (Main-1) and when you modify and save Main-1 then Main opens automatically?
      I'll have to try it out myself, I don't think I can come up with a solution otherwise.

      Delete
  35. Hi George,

    Correct, if I save a copy and call it "Main 1" I noticed that only if I unhide the sheets and make data changes to them and save the document, for some reason the original "Main" has been opened with the hidden pages still hidden...

    Do I have the Visual Basic codes entered in the correct sections?



    Thanks,
    Steve
    SPB60515@Yahoo.com

    ReplyDelete
    Replies
    1. Yes, the placement of the code is correct, that should't be an issue... My best guess is that the code that runs when you close the workbook somehow triggers for both workbooks.
      Can you try replacing the line:
      Worksheets(SheetName).Visible = xlSheetHidden
      with this:
      ActiveWorkbook.Worksheets(SheetName).Visible = xlSheetHidden
      That should force Excel to run that code only on the current workbook... No idea if this will work, but I think it's worth a try.

      Delete
    2. Shucks, I replaced the line on both workbooks - 'Main' & 'Main1' but the 'Main' gets automatically opened only when I make changes to the hidden (password protected) sheets of 'Main1." Very strange.

      If you or someone on the forum can think of a way to alleviate this, I would be so grateful. I have to open the 'Main' up for each new job then do a 'Save As' for each new design or project so I hope I'm not out of luck here...


      THANKS,
      Steve

      Delete
  36. P.S. George - whether this makes any difference, I noticed that when I click my button (to UNHIDE) the tabs on 'Main1' that's when the 'Main' sheet is opened, even before I enter my password...

    ReplyDelete
  37. George, I just found the problem!

    I had created a quick command button for my ribbon for my 'Unhide' macro and when I deleted the button and just ran the macro, it worked, ie, the original or 'MAIN' document was NOT automatically opened. What a relief. Don't know why that was happening but now problem is GONE.

    Thanks so very much for all of your help and patience. I showed my one IT guy your page and all of your help and his comment was there are a lot of good people out there helping those struggling (like me) with codes and the like.

    Thank you again and I'll visit your blog for more reference help in the future!


    THANK YOU!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


    Steve

    ReplyDelete
    Replies
    1. Yeah, in that case the button was probably tied to the macro version in the 'Main' file, so every time it started it also opened the file. I'm glad you figured it out!

      Delete
  38. Thank you again, George.

    Actually, there is one little more thing that is probably pretty easy for you...

    Regarding this code I'm using:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ' you code for hiding the sensitive sheets here:
    Dim SheetName As Variant
    For Each SheetName In Array("Est1", "Est2", "Est3")
    Worksheets(SheetName).Visible = xlSheetHidden
    Next SheetName
    Cancel = False ' this will save the file
    End Sub


    particularly this line:
    For Each SheetName In Array("Est1", "Est2", "Est3")

    What I will ACTUALLY have is a "graphics" page, "accounting" page, then up to 50 estimating sheets, ie, "Est1," "Est2," Est3,"..... "EST50"

    Is there a way to have this work so I don't have to type in every page - maybe "graphics" and "accounting" and then something that will unhide all EST sheets from 1 - 50??


    THANK YOU AS ALWAYS,
    Steve

    ReplyDelete
    Replies
    1. You can attack the problem from the other direction, meaning you check for the sheet names you want to keep and hide the rest:

      For Each sh In Sheets
      Select Case sh.Name
      Case "graphics"
      sh.Visible = xlSheetVisible
      Case "accounting"
      sh.Visible = xlSheetVisible
      Case Else
      sh.Visible = xlSheetHidden
      End Select
      Next

      You can add more Case statements for other sheet names if you need more to stay visible.

      Delete
    2. George,

      Thank you and I understand your logic - BUT - I much prefer the original method because likes to open up a new "Main" page and complete re-write the contract. The way I have it now, this is allowable because I am controlling exactly which sheets to be hidden and then unhidden upon closing out.

      If I go this other route, a Sales person might open up a new 'Main' sheet and give it another name and then save it, only to find it "gone" when they reopen because the code assumes it is to be hidden because it won't be tagged as 'Visible' - hope I'm being clear.

      I was just hoping that there would be an easy way to hide all 50 or so 'Est' sheets instead of typing each one in from 1 to 50...



      THANK YOU!!!!!!!!

      Steve

      Delete
    3. I see, that would be a problem indeed...
      VBA has another operator you can use here, Like, it matches a string (in your case the sheet name) to a pattern (ESTxx) and returns true or false. So you can write something like this to match all the sheets with names beginning with EST:

      For Each sh In Sheets
      If sh.Name Like "EST##" Then sh.Visible = xlSheetHidden
      Next

      This is available starting with Ofiice 2010. More information here: Like Operator.

      Delete
  39. Hi George,

    Thanks and I do understand your logic - BUT - the way I have it now, is that there is actually a 'Main' sheet which is the master document that us estimators can see and another 'Main' sheet copy that can be viewed by Sales only whereby they can change our wording and even pricing if they need to. This sheet will always be unhidden.

    But sometimes Sales will create a completely new 'Main' tab next to my original and start the contract proposal from scratch with completely different language. With your new method, once Sales re-opens the spreadsheet, this new sheet they've created will be "gone" so to speak..

    I was just hoping there would be an easy way to have Excel hide a few select pages ("Accounting," "Graphics") and EST1 - EST50 without me having to type every one of those sheet names.


    So if you get a second, please let me know if this is possible, otherwise I can manually type in those sheets, it's no biggie.



    THANK YOU!!!

    Steve


    If I go your revised route, a Sales person can conceivably do what I have detailed above and rename their new version of the 'Main' sheet but this sheet will be "gone" the next time they open up the document because I haven't told Excel that the status of this sheet is to be

    ReplyDelete
  40. It works but..... if you try the PASSWORD protect code (see Respuzy's variation of the code) that will work as well but one can simply bypass this and right click on the unhidden page, select, 'Unhide' and it will NOT ask for a password. Does anyone know how to use this code and have a sure-fire password protection for the hidden sheets so a smart user won't be able to do what I've mentioned without the password?


    Thank you.

    ReplyDelete
    Replies
    1. You can protect the worksheet to prevent users from un-hiding sheets and making many other changes. No need to use a macro, that functionality in built-into Excel.

      Delete
  41. George Thanks a lot buddy, You've saved my butt ;)

    ReplyDelete
  42. Hi again, George,

    Per your response above, how then do I make sure the "wrong" user doesn't unhide my sheets? As before, only estimators will see the various pricing pages, Sales will only see the Master Sheet.

    THANKS so much, George,
    Steve

    ReplyDelete
    Replies
    1. Hi Steve,

      You have two options, as far as I know, unfortunately both have some limitations:
      - you can password-protect the workbook and individual sheets and give the different passwords to the various people, e.g. Sales doesn't get any password and can only see the Master sheet, estimators get the password to unlock the workbook and can unhide sheets to see the pricing. Obviously that's not very flexible, but it has the advantage you don't have to write any code;
      - or you can try to set up a user+password system in VBA (we have something like this at work, but I don't know how it's actually coded), but this can be easily bypassed if users disable macros when they open the file...

      Delete
    2. George - thank you for all of your help!
      I've been meaning to thank you earlier but my new worksheet project has been on and off as we've been swamped in the Estimating dept.

      But truly, a great thank you for all of your help, I would never have gotten off the ground without you.

      I'll try both of your options and let you know.


      Best,
      Steve

      Delete
  43. This was EXTREMELY helpful. THANKS!!!

    ReplyDelete
  44. Thank you for sharing this. It is saving me from unhiding 12 sheets in 8 different workbooks!

    ReplyDelete
  45. Hi All,

    Is it possible to place simple code so as to:
    1.make only certain sheets (in an array) visible
    2. all the rest hidden

    For Each s In Sheets(Array("Sheet1", "Sheet2"))
    s.Visible = True
    Next

    part one seems to be ok however I do not have Idea how to address point to. by stating that sheets should not belong to previously stated range or in some other way.

    help would be much appreciated

    ReplyDelete
    Replies
    1. Hi,
      Well, if you are starting with the sheets hidden, your code would unhide only sheets in the array, so there is no need to have another loop to check if the sheets out not in the array. Or have I misunderstood your question?

      Delete
    2. hi George,
      sometimes I have all the sheets visible at the begining and would like to run such code that applies to some certain sheets and while perceiving the rest of them as other selection/group that it hides (as mentioned in previous mail). I still do not know how to put it in VBA. with some exclusion/case else/ 'rest' or sth else...

      Delete
    3. If the sheet names and their final state (hidden/visible) stay the same each time you need to run the code, you can manually add an array in the codes containing the data in two columns, one for the names, the other for final state. Then you can change the code to check each sheet name versus the state in the array and hide or show it accordingly. Something like:

      For i = 1 to maxnosheets
      if Array(i, 2) = "Visible" then ' or however you define if in the original array
      Sheets(Array(i, 1)) = xlSheetVisible
      else
      Sheets(Array(i, 1)) = xlSheetHidden
      end if
      next i

      I haven't tested this code but theoretically it should work in your case.

      Delete
  46. Mr. Moga - Outstanding! Simple and elegant. Thank you.

    ReplyDelete
  47. Works great. Thanks

    ReplyDelete