CODEWITHSUNDEEP
excelvbacopyhidden
Elvis
Elvis

Reputation: 425

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

enter image description here

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

Upvotes: 0

Views: 1759

Answers (3)

Variatus
Variatus

Reputation: 14383

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

Upvotes: 2

Jonas
Jonas

Reputation: 1

Sub CopySheet6()

  Sheet6.Copy After:=Sheets(Sheets.Count)
    
  Worksheets("Sheet6 (" & Sheets.Count - 1 & ")").Visible = True

End Sub

Upvotes: -1

FaneDuru
FaneDuru

Reputation: 42236

Please, try:

Sheet6.Copy After:=Sheets(Sheets.count)
Sheets(Sheets.count).Visible = xlSheetVisible

Upvotes: 0

Related Questions