For each construct excluding the last two sheets

hgus393 · Feb 14th 2006

Hi all,

I am really stupid I cannot get a macro to exclude the last two sheets in a workbook when I am using a for each construct. I tired with the following and that did not fly:

Code

Sub Duh()


Dim wks As Worksheet


For Each wks In ActiveWorkbook.Worksheets(Worksheets.Count - 2)
...
...
Next wks
End sub

Display More

Anyone know how to exclude the two last sheets from the for each construct?

/Robert

Dave Hawley · Feb 14th 2006

Re: For each construct excluding the last two sheets

Hi Robert

Try

Code

Sub Duh()
     
    Dim lLoop As Long
     
    For lLoop = 1 To Worksheets.Count - 2
        MsgBox Worksheets(lLoop).Name
    Next lLoop
End Sub

Richie(UK) · Feb 14th 2006

Re: For each construct excluding the last two sheets

Hi Robert,

Try a slightly different approach:

Code

Sub Duh2()
    Dim i As Integer, j As Integer
    
    i = Worksheets.Count - 2
    
    If i > 0 Then
        For j = 1 To i
            MsgBox Worksheets(j).Name
        Next j
    Else
        MsgBox "Fewer than 3 worksheets!"
    End If
    
End Sub

Display More

HTH

Edit : I'm out of practice - Dave is way too quick!

hgus393 · Feb 14th 2006

Re: For each construct excluding the last two sheets

Quote from Dave Hawley

Hi Robert

Try

Code

Sub Duh()
     
    Dim lLoop As Long
     
    For lLoop = 1 To Worksheets.Count - 2
        MsgBox Worksheets(lLoop).Name
    Next lLoop
End Sub

Hi Dave,
This works really good thank you, the problem is that I am trying to run a countif formula in all the sheets except for the last two sheets.
The code looks like this:

Code

Sub Duh()


Dim wks as worksheet
Dim rowc as long


For Each wks In ActiveWorkbook.Worksheets


rowc = Application.WorksheetFunction.CountA(wks.Range("B:B"))


For i = 2 To rowc
wks.cells(i, 16) = "=COUNTIF(C[-14],RC[-14])"
Next i
Next wks
End sub

Display More

Do you have any ideas to eliminate the last two sheets from this macro?

//Robert :confused:

For each construct excluding the last two sheets

Participate now!

Share