Copy Hidden Template - Results in Error

[jwb1012]

Hi - I have a code (shown below) that is creating multiple copies of a template worksheet and number sequentially. The problem is that I would like to have the template worksheets hidden, but when I do this I get a Run Time Error 1004: Method Select of Object_Worksheet failed. If not hidden, it works as expected. The new sheets are created but stay hidden, and the naming convention gets messed up, resulting in:

Labor BOE 1 of 2 (2) ........ should be Labor BOE 1 of 2
Labor BOE 2 of 2 ........ OK
Template - BOES (2)........ should be Template - BOES (2)
Template - Tasks ........ OK


The line of code that is highlighted as the issue is Sh.Select. The goal of that section is to paste values in cell A1 of all sheets named "Labor BOE*". I wrote 3 macros and then combined them, the one in question being the 3rd step. Is it possible that I have the structure wrong? Any thoughts?

Sub GenerateBOEs()
Dim Sh As Worksheet
Dim i As Integer
Dim i2 As Integer
' Initiate message box
If MsgBox("Do you want to generate BOEs?" & Chr(10) & Chr(10) & "Warning:  All existing BOEs will be deleted!  This action cannot be undone!", vbYesNo, "Confirm") = vbNo Then
Exit Sub
Else
Application.DisplayAlerts = False
On Error Resume Next
' Delete all Labor BOEs
For Each Sh In ActiveWorkbook.Sheets
    If Left(Sh.Name, 9) = "Labor BOE" Or Sh.Name = "Export - Labor BOEs" Then
        Sh.Delete
    End If
Next Sh
Application.DisplayAlerts = True
On Error GoTo 0
End If
' Set the worksheet to copy
Set Sh = Worksheets("Template - BOEs")
' Determine number of copies to make
i = Worksheets("Staffing Plan").Range("D5")
' Name the first sheet
Sh.Name = "Template - BOEs"
i2 = ActiveWorkbook.Worksheets.Count
For X = 1 To i
    Sh.Copy After:=Sheets(i2 + X - 1)
    
    ' Name of subsequent sheets
    Sheets(X + i2).Name = "Labor BOE " & X & " of " & i
Next X
' Paste values in A1 of Labor BOEs
For Each Sh In ActiveWorkbook.Worksheets
    If Left(Sh.Name, 9) = "Labor BOE" Then
       [U][I][B] Sh.Select[/B][/I][/U]
        With Sh.Range("A1")
            .Value = .Value
        End With
    End If
        Application.CutCopyMode = False
    Next Sh
    
  
End Sub

 

[JoeMo]

You can't select a hidden sheet. But you can copy a hidden sheet w/o selecting it. If you copy it to the same workbook the hidden source sheet is in, the copy will also be hidden, but you can unhide it if desired.