' Set the range of visible cells to be copied
    Set visibleCells = ws.Range("A6:EF" & lastRow)

    fullArray = visibleCells.value

    ' Initialize filteredArray with the same number of columns as fullArray
    ReDim filteredArray(1 To UBound(fullArray, 1), 1 To UBound(fullArray, 2))

    j = 1 ' Row index for filteredArray

    For i = 1 To UBound(fullArray, 1)
        If fullArray(i, 2) = "Value I want" Then
            filteredArray(j, 1) = fullArray(i, 1)
            filteredArray(j, 2) = fullArray(i, 2)
            ' Copy other columns as needed
            j = j + 1
        End If
    Next i

    ReDim Preserve filteredArray(1 To j - 1, 1 To UBound(fullArray, 2))

    ' Activate the target worksheet
    targetws.Activate

    ' Debug print the size of the range
    Debug.Print "Number of total rows: " & UBound(fullArray, 1)
    Debug.Print "Number of total columns: " & UBound(fullArray, 2)
    Debug.Print "Number of filtered rows: " & UBound(filteredArray, 1)
    Debug.Print "Number of filtered columns: " & UBound(filteredArray, 2)

    ' Define the target range in the target worksheet and copy the data
    Set targetRange = targetws.Range("C6").Resize(filteredArray.Rows.Count, filteredArray.Columns.Count)
    targetRange.value = filteredArray.value

Immediate
Number of total rows: 46680
Number of total columns: 136
Number of filtered rows: 46680
Number of filtered columns: 136