VBA - "For each"-loop inside for-loop only works the first time

Question

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?

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)

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" & 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

Answer 1

Why not use 2 For Each loops?

Be careful with the name of your variable too, Sheet is already an element of VBA language, so I rename it to aSheet.

When you use this Workbooks("Utkast1.xlsm").Worksheets("Active"), do you mean to take the ActiveSheet or do you have a sheet named Active?

For Each aSheet In Workbooks(Filename).Worksheets
    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

    Set Ws = Workbooks("Utkast1.xlsm").Worksheets("Active")
    Ws.Cells(aSheet.Index, 5).Value = 1 'test
    'For the active product numbers in the sheet
    For Each x In aSheet.Range(aSheet.Cells(1, 1), aSheet.Cells(aSheet.Range("A" & aSheet.Rows.Count).End(xlUp).Row, 1))
        Ws.Cells(sheetNbr, 6).Value = 2 'test
        If x.Offset(0, 2).Value() = "J" Then
            Ws.Cells(j, 1).Value = aSheet.Name 'print store number
            Ws.Cells(j, 2).Value = x.Value() 'print BNR
            Ws.Cells(j, 3).Value = "URF" 'print data source
            j = j + 1
        End If
    Next x
Next aSheet

Set x = Nothing
Set y = Nothing
Set Ws = Nothing