Syntax for each worksheet in a workbook

[RRinTetons]

I have a code I want to run on each worksheet it a workbook. What's the syntax to reference each worksheet? I'm guessing there's something like

For each Sheet In Sheets

but I haven't figured it out.

Pointer?

-
Richard Ray
Jackson Hole Mountain Resort

 

[SkipVought]

Hi,

It REALLY depends on how your existing code is coded.

Please post your existing macro.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RRinTetons]

The workbook has 72 worksheets. Each is an identical report format for a different department. The data is filled in by a function that queries a SQL Server back end for each cell. Some rows end up with data for all departments, some don't. A formula off in 'user doesn't see this' land displays an "X" if the rows should be hidden, and this routine hides them. The user will update some input parameters referenced by the SQL lookups in each cell throughout the sheet and hit a button to recalc (workbook is set to Manual calculation). When that's done I want to call this routine for each sheet to hide the blank rows before the user reviews the reports and prints them to pdf's for email distribution.

Here's the little routine I want to call:

Sub HideBlankRows()
    Dim x As Range, y As Range
    For Each x In Range("Q13:Q27")
        If x = "X" Then
            If y Is Nothing Then
                Set y = x
            Else
                Set y = Union(x, y)
            End If
        End If
    Next x
    y.EntireRow.Hidden = True
End Sub

-
Richard Ray
Jackson Hole Mountain Resort

 

[SkipVought]

Sub Main()
dim ws as worksheet
for each ws in worksheets
   HideBlankRows ws
next
End Sub
Sub HideBlankRows([b]ws as worksheet[/b])
    Dim x As Range, y As Range
    For Each x In [b]ws.[/b]Range("Q13:Q27")
        If x = "X" Then
            If y Is Nothing Then
                Set y = x
            Else
                Set y = Union(x, y)
            End If
        End If
    Next x
    y.EntireRow.Hidden = True[b]
    set y = nothing[/b]
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RRinTetons]

Did you just add that 'ws.' in the For Each statement . And resetting the range accumulator to Nothing?

I figured that out and came back to note it and lo and behold, it was already corrected!

Thanks, btw, works perfectly. Since I have a bunch of hidden setup and lookup sheets I added

        If ws.Visible = True Then
            HideBlankRows ws
        End If

to avoid errors when it ran off the end of the world.

-
Richard Ray
Jackson Hole Mountain Resort