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.
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.