Activating the Current ws in Array befor Calling Sub?

[dallin01]

For the following code to work when calling THide_Rows_script sub I have to ws.Activate so each worksheet is selected before I call the sub. Is there a better/faster method of activating the current array ws?

Dim ws As Worksheet
For Each ws In Sheets(Array("qm3", "qt3", "qr3", "qp3", "qu3", "qa3", "qetc3", "qet3", _
"clcrk3", "qgmc3", "qgm3", "rgs3", "rp3", "qsi3", "mr3"))
With ws
ws.Rows("6:200").EntireRow.Hidden = False
ws.Activate
THide_Rows_Script 6, 10
End With
Next ws


Sub THide_Rows_Script(x As Long, y As Long)
Dim LR As Long
Dim rng As Range
Dim rng1 As String

LR = Cells(Rows.Count, y).End(xlUp).Row
With Range(Cells(x, y), Cells(LR, y))
Set rng = .Find("0.00", LookIn:=xlValues, LookAt:=xlWhole)
If Not rng Is Nothing Then
rng1 = rng.Address
Do
rng.EntireRow.Hidden = True
Set rng = .FindNext(rng)
Loop While Not rng Is Nothing
End If
End With
End Sub

 

[venkat1926]

do you think the array of sheet names necessary

I am giving you a sample trivial example of macros
can you use this information and modify your code

Sub testsone()
Dim ws As Worksheet
Dim j As Integer, k As Integer
j = Worksheets.Count
'MsgBox j
For k = 1 To j
Worksheets(k).Select
testtwo
Next k
End Sub

Sub testtwo()
Range("A1") = "venkat"
End Sub

this will apply ro ALL  the sheets.

if you want to exempt the sheet ("summary")

you can add a line modify the first macro 
[CODE]
Sub testthree()
Dim ws As Worksheet
Dim j As Integer, k As Integer
j = Worksheets.Count
'MsgBox j
For k = 1 To j
If Worksheets(k).Name <> "summary" Then
Worksheets(k).Select
testtwo
End If
Next k
End Sub

 

[Norie]

Why not pass the worksheet as an argument to the pub that's hiding rows?

Then you can use it in that part of the code when you need to refer to it.

If you did that you wouldn't need to activate.

 

[Norie]

Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets  
Select Case ws.Name 

         Case "qm3", "qt3", "qr3", "qp3", "qu3", "qa3", "qetc3", "qet3", _
            "clcrk3", "qgmc3", "qgm3", "rgs3", "rp3", "qsi3", "mr3"
        With ws
            ws.Rows("6:200").EntireRow.Hidden = False
            
            THide_Rows_Script 6, 10
, ws 
        End With
   Case Else 
    ' do nothing 

   End Select 
    Next ws 
 
Sub THide_Rows_Script(x As Long, y As Long, ws As Worksheet )
Dim LR      As Long
Dim rng     As Range
Dim rng1    As String
    
LR = ws.Cells(Rows.Count, y).End(xlUp).Row
With ws.Range(ws.Cells(x, y), ws.Cells(LR, y))
    Set rng = .Find("0.00", LookIn:=xlValues, LookAt:=xlWhole)
    If Not rng Is Nothing Then
        rng1 = rng.Address
        Do
            rng.EntireRow.Hidden = True
            Set rng = .FindNext(rng)
        Loop While Not rng Is Nothing
    End If
End With
End Sub