- Trending Categories
- Data Structure
- Operating System
- MS Excel
- C Programming
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
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.
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 _ after:=ActiveWorkbook.Sheets("Sheet5") 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.
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.
Kickstart Your Career
Get certified by completing the courseGet Started