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.
Best Office Productivity Tools
Supports Office/Excel 2007-2021 and 365 | Available in 44 Languages | Easy to Uninstall Completely
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...
Supercharge Your Excel Skills: Experience Efficiency Like Never Before with Kutools for Excel (Full-Featured 30-Day Free Trial)
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day! (Full-Featured 30-Day Free Trial)
