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.

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!

Originally Posted by *me*

Macro to unhide codenamed Sheet 4 to Sheet 47

Please Login or Register  to view this content.

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.

Originally Posted by dangelor

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!

Originally Posted by jindon

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!

Administrative Note:

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

Sorry. Noted.

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!

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