For...Next Loop Sticking

[azexceluser]

I am using the following code to delete the header from each worksheet in a large Excel workbook. The code is running fine until it gets to one specific worksheet, then I receive the following error message: "Run-time error '1004': Select method of worksheet class failed". I have inserted this code into other workbooks as a test, and I do not get the error, so it is something specific about this one worksheet within this one particular workbook. I have also tried deleting the code, doing a "save as" on the workbook and starting over, but the error continues to occur. Following is the code I am using, I have changed the problem area to red:

Private Sub CommandButton1_Click()

    'Variable Declaration
        Dim cnt As Integer
        Dim i As Integer
                  
    'Assign Variables
        cnt = ActiveWorkbook.Worksheets.Count - 1
        
    'Remove Headers
        Application.ScreenUpdating = False
        For i = 2 To cnt
            Worksheets(i).Select            
            With ActiveSheet.PageSetup
                .LeftHeader = ""
                .CenterHeader = ""
                .RightHeader = ""
            End With
        Next i
        Application.ScreenUpdating = True
                        
End Sub

Any ideas or suggestions would be appreciated!

 

[V_Malkoti]

Sounds like its a hidden sheet. You can choose to handle it by not activating the hidden sheet:

    For i = 2 To cnt
        If Worksheets(i).Visible = xlSheetVisible Then
            Worksheets(i).Select
            ' Any other code to run only if sheet is not a hidden sheet
        End If
        ' Code to run for all sheets
    Next i

Or you can choose to change the visible state temporarily and then restore it back:

    For i = 2 To cnt
        iShState = Worksheets(i).Visible
        Worksheets(i).Visible = xlSheetVisible
        Worksheets(i).Select
        ' Remaining code to run for the sheet
        Worksheets(i).Visible = iShState ' Restore state
    Next i

 

[azexceluser]

That appears to have been the issue - thank you! I decided to use your second option (change then restore), and it worked perfectly.

 

[darkfaith]

i would like to use this code too but it removes the headers in the NEXT sheet. How do i get the code to remove headers & footers in the active sheet?
Thanks!