Macro does not work when worksheet is hidden

[Stormseed]

Hi !

please tell me what am I doing wrong in this program ?

VBA Code:

Sub FilterCopy()
 
Application.ScreenUpdating = False

Sheets(Array("sec1", "sec2", "sec3", "sec4", "sec5")).Select
Cells.Select

Selection.ClearContents
 
Sheets("Raw Data").Activate

    [A1:m1000].AutoFilter Field:=1, Criteria1:="194A"
    [A1:m1000].Copy Sheets("sec1").[a1]
    
    [A1:m1000].AutoFilter Field:=1, Criteria1:="194C"
    [A1:m1000].Copy Sheets("sec2").[a1]
    
    [A1:m1000].AutoFilter Field:=1, Criteria1:="194I"
    [A1:m1000].Copy Sheets("sec3").[a1]
    
    [A1:m1000].AutoFilter Field:=1, Criteria1:="194J"
    [A1:m1000].Copy Sheets("sec4").[a1]
    
    [A1:m1000].AutoFilter Field:=1, Criteria1:="195"
    [A1:m1000].Copy Sheets("sec5").[a1]
    
    [A1:m1000].AutoFilter

Application.ScreenUpdating = True
 
End Sub

Execution of this code with all the worksheets visible works fine. However, if I make a worksheet (any one from sec1 to sec5) hidden or veryhidden then it throws a Debug Error "Select method of Sheets class failed" ??

Any idea why am I getting this and why am I supposed to hide these sheets before trigerring the macro ?

thanks a lot for your time

 

[Andrew Poulsom]

You can't Select a hidden sheet, so avoid it:

Sub Test()
    Dim arrSheets As Variant
    Dim i As Integer
    Application.ScreenUpdating = False
    arrSheets = Array("sec1", "sec2", "sec3", "sec4", "sec5")
    For i = LBound(arrSheets) To UBound(arrSheets)
        Sheets(arrSheets(i)).Cells.ClearContents
    Next i
    Application.ScreenUpdating = True
End Sub

 

[Stormseed]

ok, thanks for your help, sir.

I guess you gave me the code to clear the worksheets before the autofilter code is executed for fresh data to be copied, correct ?

Is the code below properly putup now ?

New code:

Sub Test()
    Dim arrSheets As Variant
    Dim i As Integer
    Application.ScreenUpdating = False
    arrSheets = Array("sec1", "sec2", "sec3", "sec4", "sec5")
    For i = LBound(arrSheets) To UBound(arrSheets)
        Sheets(arrSheets(i)).Cells.ClearContents
    Next i
 
    Sheets("Raw Data").Activate

    [A1:m1000].AutoFilter Field:=1, Criteria1:="194A"
    [A1:m1000].Copy Sheets("sec1").[a1]
    
    [A1:m1000].AutoFilter Field:=1, Criteria1:="194C"
    [A1:m1000].Copy Sheets("sec2").[a1]
    
    [A1:m1000].AutoFilter Field:=1, Criteria1:="194I"
    [A1:m1000].Copy Sheets("sec3").[a1]
    
    [A1:m1000].AutoFilter Field:=1, Criteria1:="194J"
    [A1:m1000].Copy Sheets("sec4").[a1]
    
    [A1:m1000].AutoFilter Field:=1, Criteria1:="195"
    [A1:m1000].Copy Sheets("sec5").[a1]
    
    [A1:m1000].AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[Andrew Poulsom]

Your problem was with hiding sheets, so I gave you some code that works. You can add whatever code you want to it.

 

[Stormseed]

oh
yeah, i agree
thanks again for your help ray: