How to Copy a Hidden Sheet in Excel?

We can easily copy and paste data into an Excel sheet or the entire worksheet in Excel. But it can be a very lengthy process if we try to copy the data from the hidden sheet. We can copy the data from a hidden sheet without unhiding the sheet by using the VBA application, as it can’t be done directly in Excel. Read this tutorial to learn how you can copy a hidden sheet in Excel.

Copying a Hidden Sheet in Excel

Here we will insert a VBA module and then run the code to complete the task. Let us look at a simple procedure for copying hidden sheets in Excel using the VBA application.

Step 1

Let us consider an Excel workbook that has multiple worksheets with some data. Now to hide any sheet, right-click on the sheet name and select hide from the menu.

Right-click> Hide

If we want to copy the data from the sheet, we can use the help of the VBA application. First, right-click on the sheet name and select View Code to open the VBA application.

Then, click "insert", select "module", and type the following program code in the text box as shown in the image below.

Right click > View code > Insert > Module > Programme


Sub DupSheet()
'Update By Nirmal
    Dim Actsheet As String
    Application.ScreenUpdating = False
    On Error Resume Next
    ActiveWorkbook.Sheets("Sheet5").Visible = True
    ActiveWorkbook.Sheets("Sheet5").Copy _
    ActNm = ActiveSheet.Name
    ActiveSheet.Name = InputBox("Enter the name for the new sheet.")
    Sheets(ActiveSheet.Name).Visible = True
    ActiveWorkbook.Sheets("Sheet5").Visible = False
    Application.ScreenUpdating = True
End Sub

In the program, sheet 5 is the name of the hidden sheet in the workbook.

Step 2

Now save the sheet as a macro-enabled workbook, click F5 to run the code, type the name of the sheet you want to create in the text box, and click OK.

Save > F5 > Sheet name > Ok

A sheet named "copied sheet" will be created with the data of the hidden sheet.


In this tutorial, we used a simple example to demonstrate how you can copy from a hidden sheet in Excel.

Updated on: 07-Mar-2023


Kickstart Your Career

Get certified by completing the course

Get Started