For each function, to scroll through specially named worksheets

Question

I am trying to find the right way to encode a macro that goes through 12 sheets with specific names (Jan, Feb, ..., Dec). I thought there would be a good choice for each function, so I tried the following:

dim crntSht as worksheet
set crntsht=("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
for each crntsht in worksheets
.
.
.
end for

this clearly did not work, since I did not correctly define crntsht.

Can someone suggest a better way to carry all 12 sheets at once and skip all the other sheets in one book?

thanks

Answer 1

Ah, Tim beat me ... my answer is a little different, however ...

Sub LoopThroughSheets()

    Dim Months As Variant
    Dim Month As Variant

    Months = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", _
         "Aug", "Sep", "Oct", "Nov", "Dec")

    For Each Month In Months
        'Code goes here.
    Next Month

End Sub

Answer 2

Siddhart Alternative:

dim arrSht, i 
arrSht = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", _
                "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

for i = lbound(arrSht) to ubound(arrSht)
    with worksheets(arrSht(i))
        'work with sheet
    end with
next i

Answer 3

Microsoft Excel MONTHNAME, , , 1 12.

MonthName (, [])

abbreviated optional. This parameter takes a logical value: TRUE or FALSE. If this parameter is set to TRUE, this means that the month name is abbreviated. If this parameter is set to FALSE, the month name is not abbreviated.

Example

?MonthName(1,True)

will provide you JAN

Using this for our benefit

Sub Sample()
    Dim ws As Worksheet
    Dim i As Long

    For i = 1 To 12

        Set ws = ThisWorkbook.Sheets(MonthName(i, True))

        With ws
            '
            '~~> Rest of the code
            '
        End With
    Next i
End Sub

Answer 4

I think it's probably better.

dim v as variant

for each v in thisworkbook.sheets
' do something
' msgbox v.name
' msgbox v.index
next