- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
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.
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
Program
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.
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.
Conclusion
In this tutorial, we used a simple example to demonstrate how you can copy from a hidden sheet in Excel.
700 Views
