When saving a copy of a workbook, sheet becomes unhidden

Question

I am saving a copy of a workbook as a non-macro enabled workbook with some of the backend sheets hidden. I prefer to keep the backend sheets in the workbook for record keeping.

The first sheet in the workbook is one of the hidden sheets, here named "Sheet1". For some reason, when I open a copy of the newly saved workbook, it's not hidden anymore. I can even see that it was correctly hidden after the VBA saves if I add a breakpoint between saving and closing.

Any help with figuring this out is appreciated!

Sub MyVBA()

        Dim ws As Worksheet
        Dim wsName As Variant

        'The string in quotes is what name the report should save and email as.
        wsName = "My Workbook - "

            ActiveWorkbook.Save

            'Saves a master copy
            'Name the first and last of the backend sheets you want to hide before saving a master. It will hide everything in between.
                For i = Sheets("Sheet1").Index To Sheets("Sheet5").Index
                    Sheets(i).Select Replace:=False
                Next i
                ActiveWindow.SelectedSheets.Visible = False

            ThisWorkbook.Sheets.Copy
            With ActiveWorkbook
                .SaveAs ThisWorkbook.Path & "\" & wsName & Format(Date, "yyyy-mm-dd") & ".xlsx", FileFormat:=51
                .Close
            End With

    End Sub

EDIT:

I tested it out with explicit references, and I'm getting the same issue.

            For i = Sheets("Data").Index To Sheets("Key").Index
                Sheets(i).Select Replace:=False
            Next i
            Workbooks("Test.xlsm").Windows(1).SelectedSheets.Visible = False

        Workbooks("Test.xlsm").Sheets.Copy
        With ActiveWorkbook
            .SaveAs Workbooks("Test.xlsm").Path & "\" & wsName & Format(Date, "yyyy-mm-dd") & ".xlsx", FileFormat:=51
            .Close
        End With

Hm. Definitely unhidden on mine. I've ran it a few times, and each time once I open it back up the first sheet is unhidden. — AlivePresumably
Try being explicit with the workbook, instead of using ActiveWindow, or just Sheets(i). Without that, Sheets() will refer to whatever workbook is open, which may not be the one you're expecting. — BruceWayne
False is not an explicitly correct value for the Visible property. Try xlSheetHidden or xlSheetVeryHidden. Why do you select the sheets and then hide them in a group? Since you already address each one, just set each one's Visible property. — Variatus
@BruceWayne Changing to explicit references here (provided I touched all my bases as seen in the edit above) unfortunately didn't work. Variatus: K. Davis was able to provide me with a working answer for what I have, but I will try that, thank you! — AlivePresumably

K.Dᴀᴠɪs K.Dᴀᴠɪs · Accepted Answer · 2018-01-11T00:11:32

The reason this happens is due to your sheets.copy selecting the hidden sheet1. I modified your code to activate the last sheet # in your workbook after you copy your sheets.

Sub MyVBA()

    Dim ws As Worksheet, i As Long
    Dim wsName As Variant

    'The string in quotes is what name the report should save and email as.
    wsName = "My Workbook - "

    ActiveWorkbook.Save

    'Saves a master copy
    'Name the first and last of the backend sheets you want to hide before saving a master. It will hide everything in between.

    Dim LastSht As Long
    LastSht = ThisWorkbook.Sheets.Count

    For i = Sheets("Sheet1").Index To Sheets("Sheet5").Index
        Sheets(i).Select Replace:=False
    Next i
    ActiveWindow.SelectedSheets.Visible = False

    ThisWorkbook.Sheets.Copy
    Sheets(LastSht).Activate
    With ActiveWorkbook
        .SaveAs ThisWorkbook.path & "\" & wsName & Format(Date, "yyyy-mm-dd") & ".xlsx", FileFormat:=51
        .Close
    End With

End Sub

When saving a copy of a workbook, sheet becomes unhidden

1 Answers