CODEWITHSUNDEEP
vbaexcelfor-loopinfinite-loop
Leah
Leah

Reputation: 13

Trouble with for next loop not stopping

I'm trying to get all rows on multiple sheets with cells in Column D that contain waiting copied to a sheet called waiting. This is the code. The loop is running too far causing duplicate pastes.
I think j = j + 1 is causing it but I don't know how to correct it.

Sub Waiting()

  ' unfilter entire workbook

   Dim sh As Worksheet

   For Each sh In Worksheets

      sh.AutoFilterMode = False
   Next sh

  ' copy rows to waiting sheet

  Dim c As Range
  Dim j As Integer
  Dim Target As Worksheet

  Set Target = ActiveWorkbook.Worksheets("Waiting")

  j = 1

  For Each sh In Worksheets
    For Each c In sh.Range("D1:D250")   ' Do 1000 rows
      If c = "waiting" Then
         sh.Rows(c.Row).Copy Target.Rows(j)
         j = j + 1
      End If
    Next c
  Next sh
 ' j=j+1 is causing infinite loop

End Sub

Upvotes: 1

Views: 316

Answers (1)

user4039065
user4039065

Reputation:

You need to skip over sh if sh.name = target.name.

Dim c As Range
Dim j As Integer
Dim Target As Worksheet

Set Target = ActiveWorkbook.Worksheets("Waiting")
j = 1

For Each sh In Worksheets
    if sh.name <> target.name then
        For Each c In sh.Range("D1:D250")   ' Do 1000 rows
            If c = "waiting" Then
               sh.Rows(c.Row).Copy Target.Rows(j)
               j = j + 1
            End If
        Next c
    end if
Next sh

Upvotes: 1

Related Questions