Excel VBA - Stops Selecting Visible Rows on Filter after many interations

Question

My code takes a file name from TextBox1, opens that file and indexes through all the unique values in column B. It then takes a second file, file name in TextBox2, and filters it based on the current index from the first file. It then takes the filtered results and copies them to a sheet in the new workbook. A new sheet is then generated in the new workbook to paste the next filtered result.

My issue is that I have many rows of data and for some reason the filtered data is not be selected after many iterations. My program selects all filtered data when it starts, but at some point it just begins selecting the headers instead of all the visible cells. Let me know if I am missing something or if there is a quick workaround. Thank you.

Sub NewFileGenerate()
Dim I As Integer
Dim N As Integer
Dim X As Integer
Dim IndexedCell As String

X = 1

Windows(TextBox1.Value).Activate
'Need to count only populated cells
I = Sheets(1).Columns(2).Cells.SpecialCells(xlCellTypeConstants).Count
Set Newbook = Workbooks.Add
For N = 2 To I - 1
    Application.CutCopyMode = True

    Windows(TextBox1.Value).Activate
    IndexedCell = Cells(N, 2).Value
    Windows(TextBox2.Value).Activate
    With Sheets(1)
        With .Range("A1", "Z" & I - 1)
            .AutoFilter Field:=5, Criteria1:=IndexedContract
            .SpecialCells(xlCellTypeVisible).Copy
        End With
    End With
    Newbook.Activate
    ActiveSheet.Paste Destination:=Sheets(X).Range("A1:Z1")
    Cells.Select
    Selection.Font.Size = 10
    Cells.EntireColumn.AutoFit
    Cells.Select
    X = X + 1
    If X > 3 Then
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Sheet" & X
    End If
    Application.CutCopyMode = False

Next N

End Sub

Answer 1

I'm guessing that your source worksheet (textbox2.value) has more rows than your index worksheet (textbox1.value). You set I equal to the number of rows in your index worksheet, then you tell the autofilter to only use that number of rows. You need to change the line "With .Range("A1", "Z" & I - 1)" so it picks up all of the rows in your source worksheet.