If a sheet is hidden, it cannot be accessed or edited directly. By copying it to a new visible sheet, you can access and work with the data on that sheet. Copying a hidden sheet to a visible sheet can also help you create a backup of the hidden sheet. This ensures that you have a copy of the data in case the original sheet becomes corrupted or lost.
This article will show you a great way to copy a hidden worksheet to a new worksheet through VBA code without unhiding or accessing the hidden worksheet.
Copy a Hidden Sheet to a New Visible Sheet Through VBA Code
We may want to hide certain worksheets that contain confidential or sensitive information. If we are the owner of these worksheets, we can copy the hidden worksheet in the traditional way, unhide it and create a copy in the workbook. If we are not allowed to access it, we can still copy the hidden worksheet through VBA code and we can update the data on the new copy without editing the original data.
You can use the following steps to copy a hidden sheet to a new visible sheet using VBA code.
Step1: Open the Excel workbook that contains the hidden sheet and press “Alt+F11” to open the “Visual Basic Editor“. You can also click “Visual Basic” on the “Developer” tab in the ribbon.
Step2: In the “Visual Basic Editor“, right-click on the name of the workbook in the “Project Explorer” and select “Insert > Module” to insert a new module.
Step3: In the module, write the following code:
Sub CopyHiddenSheet()
Sheets("HiddenSheet").Copy Before:=Sheets(1)
Sheets(1).Visible = True
End Sub
In this example, “HiddenSheet” is the name of the hidden sheet that you want to copy. Change it to the name of the hidden sheet that you want to copy.
Step4: Press “F5” or click the “Run” button to run the code.
This will copy the hidden sheet to a new sheet before the first sheet in the workbook and make it visible.
In this step, if you already closed “Visual Basic Editor“, you can also click the “Macros” on the “Developer” tab in the ribbon to run the “Macro“.
Click “Run” in the “Macro” dialog.
Copy a Hidden Sheet with a Different Name Through VBA Code
In this case the code “Sheets(1).Visible = True” works only for the copy of the first worksheet location. It does not work if the copy is saved in a specific location. If you want to copy the hidden worksheet to a specific location, change the “Before:=Sheets(1)” parameter to the desired worksheet location.
For example, if you want to copy the hidden worksheet to the end of the workbook, change it to “After:=Sheets(Sheet.Count)“. If you want to create a copy after the hidden sheet, change it to “After:=Sheets(“HiddenSheet”)“. This operation will only copy the sheet to a specific location, but will not set it to be visible.
If you want to copy the hidden sheet with a different name, you can write the following code.
Sub CopyHiddenSheet()
Sheets("HiddenSheet").Copy Before:=Sheets(1)
Sheets(1).Visible = True
Sheets(1).Name = "New Sheet Name"
End Sub
Here, for the “New Sheet Name“, change it to the name you want to name the copy hidden sheet.
Conclusion
After reading this post, you have gained the skills to copy hidden worksheets to visible worksheets by editing VBA code. With Visual Basic editor, you can see all visible and invisible worksheets. By editing the provided code, you can copy the worksheet you want to copy to any specific location. You can also rename the copied worksheet correctly with the VBA code.