How to copy a hidden worksheet and the new copied worksheet is not hidden?

Question

I want to copy a hidden worksheet using VBA. However, When it run the vba code, all the copied worksheets are hidden as well, may I know is there any method to copy the worksheet and the new created worksheet is not hidden? My VBA code is as follows:

Sub CopySheet()
 
Sheet6.Copy After:=Sheets(Sheets.Count)
 
End Sub

Answer 1

There are two stages of Hidden, xlSheetHidden and xlSheetVeryHidden. On my Excel 365 your code worked for normal Hidden sheets and crashed for VeryHidden sheets. In neither case was the copy hidden. However, the following code will unhide the sheet, create a visible copy and hide the original again to the same level as it was before, all of that invisible to the user. This code can therefore be used for copying any sheet, regardless of the setting of its Visible property. It should work also on older versions of Excel.

Sub CopySheet()
 
    Dim Visible     As XlSheetVisibility
    
    Application.ScreenUpdating = False      ' hide the action from view
    With Sheet6
        Visible = .Visible                  ' record the sheet's visibility setting
        .Visible = xlSheetVisible           ' make the sheet visible
        .Copy After:=Sheets(Sheets.Count)   ' create a copy (the copy will be ActiveSheet)
        .Visible = Visible                  ' reset the sheet's Visible property to what it was before
    End With
    Application.ScreenUpdating = True
End Sub