+ Reply to Thread
Results 1 to 11 of 11

Macro to unhide codenamed Sheet 4 to Sheet 47

  1. #1
    Registered User
    Join Date
    07-04-2013
    Location
    brisbane, australia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Exclamation Macro to unhide codenamed Sheet 4 to Sheet 47

    Hello

    I have created a workbook that has 47 sheets ("master worksheets") all of which are hidden except for Sheet 1 - the Index. I have written some code to create a copy of a sheet when the corresponding button is pressed. It works fine except for the fact that the copies created are no longer in the same order as the master worksheets (something to do with the index order of the worksheets because some are hidden etc.). I tried to create a workaround this by writing a macro that would unhide all the master worksheets each time before copying the worksheet needed so that they copies end up in the same order as the masters (copy function used is copy after the worksheet being copied).

    The code I have written to unhide the hidden worksheets is as follows:

    Please Login or Register  to view this content.
    Now I can't seem to work out what code to include to get the i + 1 to stop after Sheet 47 (the last of the "master worksheets") when unhiding the worksheets. After I work out how to do this, then I'm assuming I'd be able to use something similar to hide the same master worksheets once the copy has been created and placed in the right spot.

    If anyone can help me with this, it'll be greatly appreciated. If there is an easier way to do this, please share!

    If anything I've said is unclear please let me know.

    Thanks in anticipation.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,214

    Re: Macro to unhide codenamed Sheet 4 to Sheet 47

    Hi *me*,

    There are 3 ways to reference a worksheet.

    1. by tab name like Worksheets("Sheet1")
    2. by code name like Worksheets("shFirst") - you set a code name using the Properties of the sheet
    3. by tab index like Worksheets(3) - this would be the third sheet from left to right

    You need to set up a counter to do your sheets.
    Instead of using For Each you need a For Next with an integer and perhaps a Sheets.Count for the last sheet.

    Please Login or Register  to view this content.
    Site that might help are:
    https://vbaf1.com/tutorial/workbook/reference/
    Last edited by MarvinP; 04-24-2020 at 10:23 AM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    07-04-2013
    Location
    brisbane, australia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Macro to unhide codenamed Sheet 4 to Sheet 47

    Hi MarvinP

    I'm self-taught VBA so don't completely understand the counter - does it stop once it gets to Sheet47? The reason I ask is because once copies of these sheets are created I need to be able to then re-hide these "master" worksheets whilst leaving all the copies unhidden.

    I have been referencing my worksheets throughout my coding mainly using code name - i.e. Sheet1.Visible = True - I have left all of these as is rather than renaming using the Properties of the sheet.

    Thanks for your prompt reply!

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,347

    Re: Macro to unhide codenamed Sheet 4 to Sheet 47

    Quote Originally Posted by *me* View Post
    Macro to unhide codenamed Sheet 4 to Sheet 47
    Please Login or Register  to view this content.

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

    Re: Macro to unhide codenamed Sheet 4 to Sheet 47

    To unhide...
    Please Login or Register  to view this content.
    To copy...
    Please Login or Register  to view this content.
    To hide...
    Please Login or Register  to view this content.
    ____________________________________________________________
    If I've been helpful, let me know. If I haven't, let me know that too.

  6. #6
    Registered User
    Join Date
    07-04-2013
    Location
    brisbane, australia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Macro to unhide codenamed Sheet 4 to Sheet 47

    Quote Originally Posted by dangelor View Post
    To unhide...
    Please Login or Register  to view this content.
    To copy...
    Please Login or Register  to view this content.
    To hide...
    Please Login or Register  to view this content.
    I understand there is an easier way to unhide all sheets but was using a more complicated way in the hopes I could use the same way to hide the same 47 sheets I have made visible.

    As with copying, I do not wish to copy the sheet after the last existing worksheet, but rather after the worksheet that is being duplicated - the reason for this is so that regardless of which sheet is copied when, all the copied sheets are in the same order as the hidden "master" sheets.

    And for hiding, I do not wish to hide all worksheets but rather the 47 "master" worksheets.

    Thanks for your post!

  7. #7
    Registered User
    Join Date
    07-04-2013
    Location
    brisbane, australia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Macro to unhide codenamed Sheet 4 to Sheet 47

    Quote Originally Posted by jindon View Post
    Please Login or Register  to view this content.

    I will give this a go in the morning when I wake up as I'm a little too tired now to focus. Will let you know if it works for what I'm looking to achieve!

  8. #8
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    10,948

    Re: Macro to unhide codenamed Sheet 4 to Sheet 47

    Administrative Note:

    Please don't quote entire posts unnecessarily. They clutter threads and make them hard to read.
    Use the "Quick reply" instead
    Thanks

  9. #9
    Registered User
    Join Date
    07-04-2013
    Location
    brisbane, australia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Macro to unhide codenamed Sheet 4 to Sheet 47

    Sorry. Noted.

  10. #10
    Registered User
    Join Date
    07-04-2013
    Location
    brisbane, australia
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Macro to unhide codenamed Sheet 4 to Sheet 47

    Thanks jindon! While there are limitations to the coding (the code will have to be edited if new master worksheets are added to include the sheet numbers) it achieves what I was after for now! Thanks!

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,347

    Re: Macro to unhide codenamed Sheet 4 to Sheet 47

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ 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. Macro to Hide and Unhide sheet except 2
    By dobracik in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-28-2015, 03:43 PM
  2. [SOLVED] Macro to unhide sheet based on sheet name
    By Samantha McNeill in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-10-2014, 09:26 PM
  3. [SOLVED] Macro for very hidden/unhide excle sheet....
    By jaysakle in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-12-2013, 03:07 AM
  4. Macro to unhide/hide sheet(s)
    By cispus78 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-16-2008, 03:18 PM
  5. VBA/macro to unhide a sheet?
    By evalon in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-16-2008, 01:39 PM
  6. HELP!! Unhide Sheet with Macro and focus on other sheet
    By saraqpost@yahoo.com in forum Excel General
    Replies: 2
    Last Post: 05-23-2006, 02:20 PM
  7. [SOLVED] Bringing up the Unhide Sheet list via macro
    By PCLIVE in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-22-2005, 02:45 PM

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