Problem copying rows to new worksheets

[TheDudeAbides1987]

I have a lot, a lot of data to sort. I need to search column U for products and then move the row information to a new workbook for each product. There are over 1,000 different products and each one has numerous entries that are randomly distrusted within column U.

So far this is what I have but I am getting run time error 9

Option Explicit
Sub sd()
Dim prod() As String
Dim myr As Range
Dim x As Integer
Dim prods As Integer
x = 0

For Each myr In Range("BE2", Range("BE2").End(xlDown))
If myr <> myr.Offset(1, 0) Then x = x + 1
Next myr
ReDim prod(x)
x = 0
For Each myr In Range("U2", Range("U2").End(xlDown))
If myr <> myr.Offset(1, 0) Then
x = x + 1
prod(x) = myr
End If
Next myr
prods = x
For x = 1 To prods
Sheets("MBSchoolSalesq12011").Copy after:=Sheets("MBSchoolSalesq12011")
ActiveSheet.Name = prod(x)
Rows("1:1").AutoFilter
Rows("1:1").AutoFilter field:=12, Criteria1:="<>" & prod(x)

Cells.SpecialCells(xlCellTypeVisible).Delete
Rows(1).Insert
Range("a1").Resize(1, 9).Value = Array("Header 1", "Header 2", "Header 3", "Header 4", "Header 5", "Header 6", "Header 7", "Header 8", "Header 9")
Rows("1:4").Insert
Range("a1") = "Product Type: " & prod(x)
Next x
End Sub

 

[TheDudeAbides1987]

I figured out the Run time error 9.

Now it is having problems with the line:
If myr <> myr.Offset(1, 0) Then x = x + 1

It says run time error 1004; application-defined or object defined error