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?
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?
Code:
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