Copy and Unhide a Hidden Excel Template Worksheet with VBA
Question details
Copy a hidden template worksheet, then rename and unhide the copy without modifying another worksheet.

- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- A VBA macro uses the hidden Lab_Generic worksheet as a template and inserts its copy after FarmFieldsList.
- Observed behavior
- The macro may rename the wrong worksheet and leave the template copy hidden when it relies on the active sheet or an incorrect worksheet reference.
Open the workbook in a desktop version of Excel that supports VBA, and confirm that Lab_Generic and FarmFieldsList are in the workbook containing the macro. If the workbook structure is protected, unprotect it before copying or changing worksheet visibility.
Reference the copied worksheet by its position after the insertion sheet
Use this approach when the template may be hidden and ActiveSheet may not identify the new copy.
The copied worksheet is placed immediately after FarmFieldsList. Referencing that position in the workbook's Sheets collection avoids assuming that the hidden copy became active.
Excel does not permit duplicate worksheet names. If the requested name is invalid or already exists, the rename fails rather than overwriting an existing sheet.
In Excel, press Alt+F11 and select Insert > Module in the workbook that contains the template.
Paste this example into the module. It temporarily makes FarmFieldsList visible, copies Lab_Generic, and restores the insertion sheet's original visibility: Sub CopyHiddenTemplate() Dim wb As Workbook Dim anchor As Worksheet Dim wshCopy As Worksheet Dim oldVisibility As XlSheetVisibility Dim FarmFieldName As String Dim errorMessage As String Set wb = ThisWorkbook Set anchor = wb.Worksheets("FarmFieldsList") FarmFieldName = Trim(InputBox("Enter a name for the new worksheet:")) If FarmFieldName = "" Then Exit Sub oldVisibility = anchor.Visible On Error GoTo CleanUp anchor.Visible = xlSheetVisible wb.Worksheets("Lab_Generic").Copy After:=anchor Set wshCopy = wb.Sheets(anchor.Index + 1) wshCopy.Name = FarmFieldName wshCopy.Visible = xlSheetVisible ' Add values or unprotect wshCopy here if needed. anchor.Visible = oldVisibility Exit Sub CleanUp: errorMessage = Err.Description On Error Resume Next anchor.Visible = oldVisibility On Error GoTo 0 MsgBox "The copy could not be completed: " & errorMessage End Sub
Replace the sample sheet names if needed. Add assignments such as wshCopy.Range("A1").Value = yourValue using the appropriate cell and value for your workbook. Run CopyHiddenTemplate from the VBA editor or Excel's Macros dialog and enter a unique worksheet name.
If the template is protected and the macro must edit protected cells, add wshCopy.Unprotect "your-password" before the value assignments, using the actual password. Do not use Worksheets("FarmFieldName"), which refers to a literal sheet name.

Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Microsoft Office format compatibility to help you move between office suites. VBA support can vary by edition and environment, so test existing macros before relying on them.

Frequently Asked Questions
Why can ActiveSheet refer to the wrong worksheet after a copy?
A copied hidden worksheet may not become the active sheet. Instead, copy it after a known worksheet and assign the sheet immediately following that insertion point to a Worksheet variable.
How do I make the copied worksheet visible?
After assigning the copy to wshCopy, use wshCopy.Visible = xlSheetVisible. This changes the copy rather than the hidden template.
What happens if the new worksheet name already exists?
Excel raises an error when the macro assigns a duplicate name; it does not overwrite the existing worksheet. Enter a unique name and check for any incomplete copy before rerunning the macro.
How can I unprotect the copied sheet without changing the template?
Use wshCopy.Unprotect with the worksheet's password after assigning the copy to wshCopy. Unprotecting that variable targets the copied worksheet, not Lab_Generic.
Why might the macro fail to copy or unhide a worksheet?
Workbook structure protection can prevent worksheet copying or visibility changes. In Excel, check Review > Protect Workbook and unprotect the structure if necessary.




