Run time error 1004 - If myr <> myr.Offset(1, 0) Then x = x + 1

[TheDudeAbides1987]

Trying to copy rows of product information to new worksheets that are named after the products. Column BE2 has the product names without duplicates. Column U has the products randomly dispersed because items were sold at different times during the year. Hope this makes sense.

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

 

[Teeroy]

I haven't looked at this in detail but there are 2 errors i can see:

Redim Prod(x)
should probably be
Redim Preserve Prod(x)
Otherwise you empty the array at each redim.

You use x as a general counter but again as a counter inside a for next loop. When the loop ends X loses its value and you next redim the Prod array to 0 and try to set a value in Prod(1) which doesn't exist, hence the error. I think you probably meant to use
For Prods = 1 to x
for the loop. If so change each x call in the loop to Prods.