Macro to unhide/hide codenamed Sheet 4 to Sheet 47

[Ana_P]

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:

Sub BatchChange_Unhide()
' Unhides all Worksheets in the Active Workbook
Dim i As Integer, ws As Worksheet
i = 0
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Sheets
i = i + 1
On Error Resume Next
ws.Visible = True
On Error GoTo 0
Next ws
Sheet1.Activate
Application.ScreenUpdating = True
End Sub

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.

 

[XLfriend777]

Hi Ana_P - Hope this helps you get started. I found the neat code below and tried it out. It sorts all the current sheets. What I do sometimes is number the sheets in the Sheet name. Something like 1.Sheet1, 2.Sheet2, 3.AnotherName, 4.YouGetTheIdea. Then if you sort the sheets they all line up the way you want. Give it a try on a practice workbook before adjusting it for your live one.

Sub SortWorksheetsTabs()
Application.ScreenUpdating = False
Dim ShCount As Integer, i As Integer, j As Integer
ShCount = Sheets.Count

For i = 1 To ShCount - 1
    For j = i + 1 To ShCount
        If UCase(Sheets(j).Name) < UCase(Sheets(i).Name) Then
            Sheets(j).Move before:=Sheets(i)
        End If
    Next j
Next i

Application.ScreenUpdating = True
End Sub

 

[Ana_P]

Hi Ana_P - Hope this helps you get started. I found the neat code below and tried it out. It sorts all the current sheets. What I do sometimes is number the sheets in the Sheet name. Something like 1.Sheet1, 2.Sheet2, 3.AnotherName, 4.YouGetTheIdea. Then if you sort the sheets they all line up the way you want. Give it a try on a practice workbook before adjusting it for your live one.

Hi goesr

I gave the code a go and it seems to do exactly what I need it to do. I'm thinking the best way to incorporate it into my copy worksheet coding is to have your code above written as is and then use a line to activate that code within my copy worksheet coding. Would including SortWorksheetsTabs be sufficient to include within my code or would I have to add additional lines?

My code for copying is as follows (have written the same lines of code for each separate worksheet):

Sub SignedDocumentsCopy()
'Creates a copy of "Signed Documents"
    Dim FYear As Integer
    FYear = Year(Range("Period_End"))
    Application.ScreenUpdating = False
    Sheet47.Visible = True
    Sheet47.Copy _
        After:=Sheet47
    On Error Resume Next
    ActiveSheet.Name = Sheet47.Name & " (" & FYear & ")"
    Sheet47.Visible = False
    Sheet1.Activate
    Application.ScreenUpdating = True
End Sub

Thanks heaps for your help with the sorting of the worksheets! Spent numerous hours trying to work out how to make this work and your code worked like a charm (and much more uncomplicated than I thought it would be!)!

Thanks
Ana

 

[Ana_P]

Oops!! Turns out I didn't quite understand the fact that it applies to the tab name so it worked well in my practice workbook (because I left the Sheet names as Sheet1, Sheet2, Sheet3 for time's sake) but when I applied it to my actual workbook it messed up the whole order of the worksheets

I don't have enough characters available to number the sheet names as you mentioned in your first post unfortunately! So does that mean that your code won't work otherwise?