VBA & Macro Problems

Copy and Unhide a Hidden Excel Template Worksheet with VBA

Steve KSteve K Nov 18, 2026 868 views

Question details

Copy a hidden template worksheet, then rename and unhide the copy without modifying another worksheet.

Copy and Unhide a Hidden Excel Template Worksheet with VBA
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

In Excel, press Alt+F11 and select Insert > Module in the workbook that contains the template.

2
Add the copy macro

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

3
Customize and run the macro

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.

4
Unprotect the copy if required

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.

Reference the copied worksheet by its position after the insertion sheet
Check for an incomplete copy: If an error occurs after the copy is created, the copy may remain in the workbook under its generated name and may still be hidden. Check Excel's sheet tabs and Unhide dialog before running the macro again.
Free Microsoft Office alternative

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.

Open and edit Microsoft Excel XLSX and XLSM workbooksUse a familiar spreadsheet interface to work with worksheets and templatesMove between common Microsoft Office document formatsWork in a free, lightweight office suite
microsoft office alternative - wps office

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.