# Problem copying rows to new worksheets

#### TheDudeAbides1987

##### New Member
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
Rows("1:4").Insert
Range("a1") = "Product Type: " & prod(x)
Next x
End Sub

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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

Replies
11
Views
425
Replies
1
Views
110
Replies
2
Views
242
Replies
1
Views
85
Replies
1
Views
397

1,196,483
Messages
6,015,460
Members
441,897
Latest member
erma

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back