How to copy a hidden sheet in Excel? 

In Excel, we can move and copy a sheet to current or other workbook quickly and easily, but have you ever tried to copy a hidden sheet to active workbook? This article, I will introduce a VBA code for you to copy a specific hidden sheet to active Excel file.

Copy a hidden worksheet to active workbook with VBA code

Copy a hidden worksheet to active workbook with VBA code

To copy a hidden sheet from a workbook, and give it a name, the following VBA code can do you a favor:

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Copy a hidden worksheet to active workbook:

Sub DupSheet()
'Updateby Extendoffice 20161109
    Dim Actsheet As String
    Application.ScreenUpdating = False
    On Error Resume Next
    ActiveWorkbook.Sheets("Sheet12").Visible = True
    ActiveWorkbook.Sheets("Sheet12").Copy _
    after:=ActiveWorkbook.Sheets("Sheet12")
    ActNm = ActiveSheet.Name
    ActiveSheet.Name = InputBox("Enter the name for the new sheet.")
    Sheets(ActiveSheet.Name).Visible = True
    ActiveWorkbook.Sheets("Sheet12").Visible = False
    Application.ScreenUpdating = True
End Sub

Note: In the above code, Sheet 12 is the hidden sheet that you want to copy, please change it to your need.

3. And then press F5 key to run this code, a prompt box will pop out to remind you specify a name for the new copy of the hidden worksheet, see screenshot:

4. Then click OK button, and the hidden sheet has been copied with the new name in active workbook at once.