How to loop for 3 worksheets

[rksample]

Hi,

I want to loop for 3 worksheets to do the same tasks. The task list is huge with about 50 lines of code. The names of the sheets are "Month 1", "Month 2", "Month 3". And in the task, I need to shift between the Month sheet and another Source sheet. So based on the Month, I need to compare a different col in the source sheet. Therefore I need to assign the selected Month sheet to a Variable, so that I can loop till end and then pass on to the next Month sheet. This continues for all the Month sheets.

Will this work?

Set wsSP = Worksheets("Source")
For Each ws In Worksheets
    
    If ws.Name Like "*Month*" Then
    
    Set wsT = Worksheets(wsName)

        Select Case ws.Name
        
        Case Is = "Month 1"
            SCol = 4
        Case Is = "Month 2"
            SCol = 5
        Case Is = "Month 3"
            SCol = 6
        End Select
            
        Do Until eMonth < 12 And eDay < 31
            
            wsSP.Select
            Set foundcell = Range("B:B").Find(what:=sDate, LookIn:=xlFormulas)
            MsgBox foundcell.Row
            SPRow = foundcell.Row
            Value = Cells(SPRow, SPCol).Value

            wsT.Select 'Please check if this is correct
            'some more tasks here

        Loop            
Next ws

Thanks in advance,
Regards,
Ravi.

 

[tlowry]

‘I think this will work more efficiently.

'===========================================================
' It's not clear from the code what needs to be done
' However, here is a start.
'===========================================================
Option Explicit
Private aws()
Private aCol()
Sub Finder()
Dim i As Integer, m As Integer
aws() = Array("Month 1", "Month 2", "Month 3")
aCol() = Array(4, 5, 6)
For m = 1 To Worksheets.Count
For i = 0 To UBound(aws)
Select Case Worksheets(m).Name = aws(i)
Case True
Do50Lines m, i
Exit For
Case Else
End Select
Next i
Next m
End Sub
Sub Do50Lines(i As Integer, k As Integer)
' This is were the 50 lines of code go using
' i the sheet number and k a index to aCol
MsgBox Sheets(i).Name & " " & aCol(k)
End Sub

 

[rksample]

Thank you Tlowry. I got my code to work. But this seems to be a much better alternative. I never used arrays before. Let me give it a try. I'll let you know.

BTW, Welcome to the Forum Tlowry. You'll have a good time here.

Thanks for the help.

cheers,
Ravi.