to write a loop until activesheet

Question

I want to check the data of activesheet  "BC" column  in all other sheet in column "F"   before active sheet. How to give a loop to read all the sheets before active active sheet. Please help. The following is my code

Sub CheckIpCode()

Dim mytext As String

Dim sp_price As Double

Dim dst1 As Worksheet

Dim dst2 As Worksheet

Dim src As Worksheet

Dim LastRowSrc As Long

Dim LastRowDst As Long

Dim r1 As Range

Dim strFound As Boolean

'Set dst1 = Sheets(1)

'Set dst2 = Sheets(2)

Set dst4 = Sheets(4)

Set src = ActiveSheet

With src

     LastRowSrc = .Cells(.Rows.Count, "B").End(xlUp).Row

End With

currow = ActiveCell.Row

curColmn = ActiveCell.Column

For irow = currow To LastRowSrc

 Do Until ActiveSheet // Here I have to write the loop  until active sheets

   Set dstSheet = ActiveWorkbook.Worksheets(I)

  With dstSheet

     Set LookUpRng1 = .Range("F1").EntireColumn

  End With

  ' strFound = True

   myVal = src.Cells(irow, "BC").Value

      If IsNumeric(myVal) Then

         res1 = Application.Match(CLng(myVal), LookUpRng1, 0)

    End If

     If IsError(res1) = True Then

         res1 = Application.Match("" & myVal, LookUpRng1, 0)

     End If

     If IsError(res1) = True Then

        Set r1 = src.Cells(irow, "BC").EntireRow

        N = dst4.Cells(Rows.Count, 1).End(xlUp).Row + 1

        r1.Copy Sheets(4).Cells(N, 1)

     Else

        Exit For

     End If

Loop

Next

End Sub

Many Thanks

Pol

Answer 1

Hi again

Intested

Sub CheckIpCode()

Dim mytext As String

Dim sp_price As Double

Dim dst1 As Worksheet

Dim dst2 As Worksheet

Dim src As Worksheet

Dim LastRowSrc As Long

Dim LastRowDst As Long

Dim r1 As Range

Dim strFound As Boolean

Dim iSheet As Long

'Set dst1 = Sheets(1)

'Set dst2 = Sheets(2)

Set dst4 = Sheets(4)

Set src = ActiveSheet

With src

     LastRowSrc = .Cells(.Rows.Count, "B").End(xlUp).Row

End With

currow = ActiveCell.Row

curColmn = ActiveCell.Column

For irow = currow To LastRowSrc

 For iSheet = 1 To src.Index - 1

   Set dstSheet = ActiveWorkbook.Worksheets(iSheet)

  With dstSheet

     Set LookUpRng1 = .Range("F1").EntireColumn

  End With

  ' strFound = True

   myVal = src.Cells(irow, "BC").Value

      If IsNumeric(myVal) Then

         res1 = Application.Match(CLng(myVal), LookUpRng1, 0)

    End If

     If IsError(res1) = True Then

         res1 = Application.Match("" & myVal, LookUpRng1, 0)

     End If

     If IsError(res1) = True Then

        Set r1 = src.Cells(irow, "BC").EntireRow

        N = dst4.Cells(Rows.Count, 1).End(xlUp).Row + 1

        r1.Copy Sheets(4).Cells(N, 1)

     Else

        Exit For

     End If

   Next iSheet

Next irow

End Sub

HTH

Regards

JY

Answer 2

Hi

something like

Public Sub loopSheets()

Dim i As Long

Dim src As Worksheet

For i = 1 To ActiveSheet.Index - 1

   Set src = Sheets(i)

   Debug.Print src.Name

Next i

Set src = Nothing

End Sub

Regards

JY