For Each Function, to loop through specifically named worksheets

Question

I'm trying to figure out the right way to code a macro that goes through 12 worksheets with specific names (Jan,Feb,...,Dec). I thought maybe for each will be a good choice 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 did not work since I defined crntsht in the wrong manner.

Can anyone suggest the best way to loop through all 12 sheets once each, and skip all other sheets in the same workbook?

Solution 1

Ah, Tim beat me... my answer is slightly 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

Solution 2

Alternative to Siddharth's answer:

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

Solution 3

Use the Microsoft Excel MONTHNAME function which returns a string representing the month given a number from 1 to 12.

Syntax

MonthName( number, [ abbreviate ] )

abbreviate is optional. This parameter accepts a boolean value, either TRUE or FALSE. If this parameter is set to TRUE, it 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 give you JAN

Using this to 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

Comments

[Solaire]

I'll search more about the lbound to ubound for now i cant tell how it exactly functions, but thank you very much for your help!

[Tim Williams]

@Solaire - Array(...) creates an array of whatever the parameters are (in this case a list of month names). Typically (but not always) the first element in the array has index=0. To be sure, we don't assume the first index is zero, and use lbound() to find the first index: likewise ubound() gives us the last index.