Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

For Each Function, to loop through specifically named worksheets

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?

like image 713
Solaire Avatar asked Feb 13 '14 17:02

Solaire


2 Answers

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
like image 133
ARich Avatar answered Nov 15 '22 04:11

ARich


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
like image 26
Tim Williams Avatar answered Nov 15 '22 06:11

Tim Williams