Selectively make a sheet in a new workbook hidden

[Rickinnocal]

Hello again...

I have a command button that creates a new workbook with a number of worksheets in it, populated from the "Parent" workbook.

Depending on the job, some of the sheets will be blank, and can be hidden. At the moment the workbook opens with all sheets visible. I'm trying to code in the decision as to whether to hide a sheet or not.

This is how I'm trying it...

Private Sub cmdCMAeFile_Click()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

    'Path to store new file
    sPath = "C:\"
    sFileName = InputBox("Enter file name for new BQS eFile. Recommend Ship Name / Port / BQS eFile")
    oFileName = ActiveWorkbook.Name
    
    'set the sheet to be copied
    Set wsCopy = ThisWorkbook.Worksheets("Prelim (FO)")
    Set wb = Workbooks.Add
    wb.SaveAs Filename:=(sFileName)
    Set wsPaste = wb.Sheets(1)
    
 'Copy everything from copy sheet
    wsCopy.UsedRange.Copy
    'Paste Values and Formats only
    wsPaste.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
    wsPaste.Range("A1").PasteSpecial xlPasteFormats
    wsPaste.Range("A1").PasteSpecial xlPasteColumnWidths
    ActiveSheet.Name = "Prelim (FO)"
    If Cells(26, 2).Value = 0 Then
    ActiveSheet.Visible = xlSheetHidden
    End If
    ActiveSheet.Protect

The code then repeats itself several times to add the rest of the sheets. However, when run it stops at the line

ActiveSheet.Visible = xlSheetHidden

with an error "Unable to set the Visible property of the Worksheet class".

If I put something in the target cell so that the If / Then is false, it skips the line and proceeds as expected.

What am I doing wrong?

 

[Fluff]

At that point in the code you only have one sheet in the new workbook, which is why you can't hide it.

 

[Rickinnocal]

At that point in the code you only have one sheet in the new workbook, which is why you can't hide it.

Ah, yes, that makes sense. Thanks.

 

[Fluff]

Glad to help & thanks for the feedback.