+ Reply to Thread
Results 1 to 42 of 42

Macro to make an Array of Worksheets VeryHidden

  1. #1
    Registered User
    Join Date
    03-11-2019
    Location
    Chicago, USA
    MS-Off Ver
    365
    Posts
    29

    Macro to make an Array of Worksheets VeryHidden

    I'm currently using the following code to make selected worksheets VeryHidden:

    Please Login or Register  to view this content.
    Rather than having to go through my entire workbook to select the same worksheets that I want VeryHidden, can I change the code above to incorporate an Array where I list the specific worksheets that I want VeryHidden?

  2. #2
    Registered User
    Join Date
    03-11-2019
    Location
    Chicago, USA
    MS-Off Ver
    365
    Posts
    29

    Re: Macro to make an Array of Worksheets VeryHidden

    Not sure why those weird characters appear at the end of the coding

  3. #3
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Macro to make an Array of Worksheets VeryHidden

    Possibly...
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    03-11-2019
    Location
    Chicago, USA
    MS-Off Ver
    365
    Posts
    29

    Re: Macro to make an Array of Worksheets VeryHidden

    Thanks for the quick reply. I tried your code . . . it comes back with the Error Handler Message, "A workbook must contain . . ."

  5. #5
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Macro to make an Array of Worksheets VeryHidden

    Try removing the error handler and see what actual error is displayed.

  6. #6
    Registered User
    Join Date
    03-11-2019
    Location
    Chicago, USA
    MS-Off Ver
    365
    Posts
    29

    Re: Macro to make an Array of Worksheets VeryHidden

    I get a RunTime error 9
    Subscript out of Range

    When I choose debug, this line is yellow: For Each wks In ThisWorkbook.Worksheets(Array("Sheet1", "Sheet2"))

  7. #7
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Macro to make an Array of Worksheets VeryHidden

    Does your workbook have worksheets with those names?

  8. #8
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Macro to make an Array of Worksheets VeryHidden

    One of the sheet names must be wrong. Note: the code is referring to the workbook containing the code, not necessarily the active workbook.
    Rory

  9. #9
    Registered User
    Join Date
    03-11-2019
    Location
    Chicago, USA
    MS-Off Ver
    365
    Posts
    29

    Re: Macro to make an Array of Worksheets VeryHidden

    The Sheet Names are correct. (Do I need to change anything else in the code you sent?)

  10. #10
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Macro to make an Array of Worksheets VeryHidden

    Try changing the names of your worksheets and then putting those same names in the code's array.

  11. #11
    Registered User
    Join Date
    03-11-2019
    Location
    Chicago, USA
    MS-Off Ver
    365
    Posts
    29

    Re: Macro to make an Array of Worksheets VeryHidden

    Same error.

  12. #12
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Macro to make an Array of Worksheets VeryHidden

    Do you have more than one workbook open? If so, close all but the one you have the code in and run it again.

  13. #13
    Registered User
    Join Date
    03-11-2019
    Location
    Chicago, USA
    MS-Off Ver
    365
    Posts
    29

    Re: Macro to make an Array of Worksheets VeryHidden

    only one open

  14. #14
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Macro to make an Array of Worksheets VeryHidden

    Can you post the workbook?

  15. #15
    Registered User
    Join Date
    03-11-2019
    Location
    Chicago, USA
    MS-Off Ver
    365
    Posts
    29

    Re: Macro to make an Array of Worksheets VeryHidden

    It's got a lot of sensitive financial information.

  16. #16
    Registered User
    Join Date
    03-11-2019
    Location
    Chicago, USA
    MS-Off Ver
    365
    Posts
    29

    Re: Macro to make an Array of Worksheets VeryHidden

    Just created a new worksheet with Sheet1 and Sheet2. Ran the macro and received this error: Run Time error 1004, Method Visible of Object '_Worksheet' failed After debug, this was highlighted yellow: wks.Visible = xlSheetVeryHidden

    Here's the current code that I'm using:
    Please Login or Register  to view this content.

  17. #17
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Macro to make an Array of Worksheets VeryHidden

    Try this workbook on your system.
    Attached Files Attached Files

  18. #18
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Macro to make an Array of Worksheets VeryHidden

    I believe Excel needs more than one item in an array

  19. #19
    Registered User
    Join Date
    03-11-2019
    Location
    Chicago, USA
    MS-Off Ver
    365
    Posts
    29

    Re: Macro to make an Array of Worksheets VeryHidden

    I created Sheet1 and Sheet2

  20. #20
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Macro to make an Array of Worksheets VeryHidden

    Please Login or Register  to view this content.
    This array contains only one worksheet.

  21. #21
    Registered User
    Join Date
    03-11-2019
    Location
    Chicago, USA
    MS-Off Ver
    365
    Posts
    29

    Re: Macro to make an Array of Worksheets VeryHidden

    OK. I added Sheet3 to the WB and Sheet2 to the array code. Now I'm getting the original error Runtime 9

  22. #22
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Macro to make an Array of Worksheets VeryHidden

    Have you tried the file in post #17?

  23. #23
    Registered User
    Join Date
    03-11-2019
    Location
    Chicago, USA
    MS-Off Ver
    365
    Posts
    29

    Re: Macro to make an Array of Worksheets VeryHidden

    I did (without enabling the macros). The macro I have set up is in my personal macro file. I wonder if this is related to the fact that I'm using Excel on a Mac, and the VBA code is not functioning properly.

  24. #24
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Macro to make an Array of Worksheets VeryHidden

    Are you SURE there isn't a leading or trailing space in the tab name? It sounds like, as has been mentioned, one of the names does not exist.

    Edit: Now you say personal workbook, you probably want to change "ThisWorkbook" to ActiveWorkbook unless the sheets in question are in the personal workbook and not some other one.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  25. #25
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Macro to make an Array of Worksheets VeryHidden

    This code is written for the workbook that contains the code. You either need to copy this code into the workbook that uses it or rewrite the code to directly reference the workbook you want to change.

  26. #26
    Registered User
    Join Date
    03-11-2019
    Location
    Chicago, USA
    MS-Off Ver
    365
    Posts
    29

    Re: Macro to make an Array of Worksheets VeryHidden

    That's it . . . changing the name to ActiveWorkbook made it work perfectly!! Thank you!!

    May I ask a followup question please? Is there a way to not only list specific worksheet names (like sheet1, sheet2), but a range of worksheets . . . "sales of aaa" through "sales of zzz". I have over 90 worksheets in this workbook, and there are sections of the workbook that I will add worksheets to and remove worksheets. So if I could state a range of worksheets, everything between them will be captured by the Array.

  27. #27
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Macro to make an Array of Worksheets VeryHidden

    You may want to do it differently then... For example:

    Please Login or Register  to view this content.
    This assumes that all sheets affected should start with "sales of ", and that no sheets that should NOT be affected have this naming convention.

  28. #28
    Registered User
    Join Date
    03-11-2019
    Location
    Chicago, USA
    MS-Off Ver
    365
    Posts
    29

    Re: Macro to make an Array of Worksheets VeryHidden

    I was using "Sales of" just as an example. I have a couple of ranges with people's last names and different project names. All the people are together and all the projects are together. So I could create spreadsheets that are People Start and People End. If there's a way to state that range in the code, then whenever I add or subtract people, I don't have to go into the code to add their names. The range feature would grab everyone between those two spreadsheets

  29. #29
    Registered User
    Join Date
    03-11-2019
    Location
    Chicago, USA
    MS-Off Ver
    365
    Posts
    29

    Re: Macro to make an Array of Worksheets VeryHidden

    Can ranges be used for worksheets is my question?

  30. #30
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Macro to make an Array of Worksheets VeryHidden

    Assuming you put People Start at the beginning of your sheets to be hidden, and People End after the last one:

    Please Login or Register  to view this content.
    Alternatively, yes Ranges could be used as well, but then we'd need a simple mock up of your setup to be able to customize the code. When I say range, I mean cells or group of cells, so the code could actually look for the sheets based on a list in a spreadsheet. However, sounds like the code I put here is what you really meant by "ranges" as a concept.
    Last edited by Arkadi; 08-07-2019 at 02:34 PM.

  31. #31
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Macro to make an Array of Worksheets VeryHidden

    "ActiveWorkbook" is optional in this case, since if no workbook is specified, Active one is assumed.

  32. #32
    Registered User
    Join Date
    03-11-2019
    Location
    Chicago, USA
    MS-Off Ver
    365
    Posts
    29

    Re: Macro to make an Array of Worksheets VeryHidden

    Perfect!! Works as expected. Now how do I combine the two? (the range with a few other specific worksheets) . . . People Start to People End, as well as specific sheets: "Cash Flow", "Financial Position", etc.

  33. #33
    Registered User
    Join Date
    03-11-2019
    Location
    Chicago, USA
    MS-Off Ver
    365
    Posts
    29

    Re: Macro to make an Array of Worksheets VeryHidden

    Also, how do I have two to three ranges in the code: People Start to People End, Project Start to Project End, etc. Do I need commas and parenthesis?

  34. #34
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Macro to make an Array of Worksheets VeryHidden

    No, 2 options... We use an array (but not exactly as before) or we just write separate blocks doing the same thing but where we repeat the code with different start and end sheets. No computer right now but ill post a code later that will make it easy for you to just add new start and end sheet names.

  35. #35
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Macro to make an Array of Worksheets VeryHidden

    As far as the specific sheets, you would use the original array method

  36. #36
    Registered User
    Join Date
    03-11-2019
    Location
    Chicago, USA
    MS-Off Ver
    365
    Posts
    29

    Re: Macro to make an Array of Worksheets VeryHidden

    Having separate ranges doing the same thing would be great. Concerning the specific sheets, yes, the code you provided earlier works great for the specific sheets. Is there a way to add that code to the macro with the different ranges, so with one click of a macro button, I will be able to hide both ranges and specific sheets?

  37. #37
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Macro to make an Array of Worksheets VeryHidden

    Certainly can be done... Just need to make some time to get on my laptop and post new code. I do understand what you mean by ranges, but to be clear a range is a cell or group of cells, and other users may get confused by that term when you use it this way.

  38. #38
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Macro to make an Array of Worksheets VeryHidden

    Give this a shot:

    Please Login or Register  to view this content.

  39. #39
    Registered User
    Join Date
    03-11-2019
    Location
    Chicago, USA
    MS-Off Ver
    365
    Posts
    29

    Re: Macro to make an Array of Worksheets VeryHidden

    I had first made an error of spelling and got an error. Once I corrected it, the macro works flawlessly. I really appreciate all your help (and patience) in getting this to work!!!
    Last edited by sduttonusa; 08-08-2019 at 10:57 AM.

  40. #40
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Macro to make an Array of Worksheets VeryHidden

    That suggests that one of your sheets in either the start array or end array is incorrect. I know the code ought to work because I tested it on my end. Check for typos? or there might be an accidental space at the end of a tab name?
    Also, when the error comes up, check what the value of i is, that will tell you at least which set of start/end elements is causing the problem. For he first element in each array i will be 0 not 1.

  41. #41
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Macro to make an Array of Worksheets VeryHidden

    Glad you got it sorted, and that I was able to help you solve it. Please remember to mark the thread as solved if you are satisfied? Thanks!

  42. #42
    Registered User
    Join Date
    03-11-2019
    Location
    Chicago, USA
    MS-Off Ver
    365
    Posts
    29

    Re: Macro to make an Array of Worksheets VeryHidden

    Yes . . . it was an extra space. Thank you!! I'm very happy to get this working.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How to make this macro applicable all the worksheets in my workbook?
    By Hristo Vladimirov in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-21-2018, 03:25 AM
  2. How Do You Make a Formula Into VBA Macro For Multiple Worksheets?
    By rmccain in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-21-2015, 11:32 AM
  3. [SOLVED] How do I make my macro run in all worksheets
    By redzan in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-23-2014, 09:04 AM
  4. Macro to make URLs Live In Worksheets
    By tdm in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-29-2012, 03:19 AM
  5. [SOLVED] How to make an array of workbooks made up of an array of worksheets?
    By 111StepsAhead in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-16-2012, 08:33 AM
  6. VLookUP ARRAY , how can I make it not relative in macro?
    By crowdx42 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-22-2006, 06:13 PM
  7. veryhidden sheet make restrictions ?!
    By Marie J-son in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-29-2005, 01:06 AM

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