Copy Hidden Template - Results in Error

jwb1012

Board Regular
Joined
Oct 17, 2016
Messages
167
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?



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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,435
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top