title: VBA - "For each"-loop inside for-loop only works the first time
body: <p>I am trying to have a "for each"-loop with a range inside a for-loop with sheets. It works just fine the first time (for sheet1) but during the second lap the code stops (error 400) between test 1 and 2, which have to mean in the for "each-loop". Does anyone know why?</p>
  
  <p>My declarations (Dim directory As String, fileName As String, sheet As Worksheet, i As Integer, j As Integer 
      Dim x As Range, y As Range, urfFile As String, sheetNbr As Integer)</p>
  
  <pre><code>For sheetNbr = 1 To Workbooks(fileName).Worksheets.Count
          j = NoDataActive() 'see which row typing should start on 
  
      Set x = Workbooks(fileName).Worksheets(sheetNbr).Range("a2") 'start active search at A2
      Set y = Workbooks(fileName).Worksheets(sheetNbr).Range("a2") 'start possible search at A2
      Workbooks("Utkast1.xlsm").Worksheets("Active").Cells(sheetNbr, 5).Value = 1 'test
  
      'For the active product numbers in the sheet
      For Each x In Workbooks(fileName).Worksheets(sheetNbr).Range(Cells(1, 1), Cells(Range("A" &amp; Rows.Count).End(xlUp).Row, 1))
  
          Workbooks("Utkast1.xlsm").Worksheets("Active").Cells(sheetNbr, 6).Value = 2 'test
  
          If x.Offset(0, 2).Value() = "J" Then
              Workbooks("Utkast1.xlsm").Worksheets("Active").Cells(j, 1).Value = Workbooks(fileName).Worksheets(sheetNbr).name 'print store number
              Workbooks("Utkast1.xlsm").Worksheets("Active").Cells(j, 2).Value = x.Value() 'print BNR
              Workbooks("Utkast1.xlsm").Worksheets("Active").Cells(j, 3).Value = "URF" 'print data source
              j = j + 1
          End If
      Next x
  Next sheetNbr
  </code></pre>