Error Handling on Copy Sheets

**ker9** · 12-01-2011, 10:22 AM

Hi, The code below runs the first If statement even if sheets are visible. How do I check if no sheets are visible to make this work? (If I remove the first If statement, the remainder works as it should, but there is no copy made if there are no visible sheets and no error message.)

Dim ws As Worksheet
For Each ws In Worksheets
    
    If ws.Name <> "Select" And ws.Visible = False Then
        MsgBox "No worksheets available to copy - please make selections using the Open Selection Form button."
        Exit Sub
        
        ElseIf ws.Name <> "Select" And ws.Visible = True Then
            ws.Copy
                Cells.Select
                Selection.Rows.AutoFit
                Rows(1).RowHeight = 65
                Range("A1").Select
        End If
    Next

**Kelshaer** · 12-01-2011, 11:14 AM

I hope i got it right.

**Kelshaer** · 12-01-2011, 11:15 AM

Dim ws As Worksheet
Dim chkExist As Boolean
For Each ws In Worksheets

    If ws.Name <> "Select" And ws.Visible = True Then
        chkExist = True
        ws.Copy
        Cells.Select
        Selection.Rows.AutoFit
        Rows(1).RowHeight = 65
        Range("A1").Select
        Exit For
    End If
Next

If chkExist = False Then
    MsgBox "No worksheets available to copy - please make selections using the Open Selection Form button."
End If