subscript out of range (newbie)

[Guest]

ok, im very new, so im learning as i go, i keep getting error "Subscript out
of range"

when i use this, if i watch the sheets, it starts to do what it is supposed
to do, then i get the error..

could someone please help me
---

Sub CopyToSheets()

Dim rng As Range
Dim oCell As Range

Set rng = Range("B4:B" & Cells(Rows.Count, "B").End(xlUp).Row)
For Each oCell In rng
oCell.EntireRow.Copy _
Destination:=Sheets(oCell.Value).Range("A65536").End(xlUp).Offset(1,
0)
Next oCell


End Sub

 

[Myrna Larson]

Again, you need to tell us on what line you get the error. Are you stepping
through the code with the F8 key in the VB Editor? You should be...

 

[Dave Peterson]

I'd guess that you have at least one value in B4:B## that has a value that
doesn't correspond to a worksheet name.

Maybe you have a typo or extra spaces (leading/trailing/embedded) that makes
your code fail.

 

[Guest]

Ok, i have the code working now, but its not working as intended. i dont get
any errors.. but its not moving all the data, only some of it, the names are
correct, it just dosn't move them. For example, it skips everything on the
"BOB" sheet, even though there is plenty of things that match that criteria,
but the "RES" sheet has everythign move over just fine.. here is the code i
have that produces no errors

Sub CopyToSheets()

Dim rng As Range
Dim oCell As Range

Set rng = Range("B4:B" & Cells(Rows.Count, "B").End(xlUp).Row)
For Each oCell In rng
oCell.EntireRow.Copy _
Destination:=Sheets(oCell.Value).Range("A65536").End(xlUp).Offset(1,
0)
Next oCell


End Sub

 

[Dave Peterson]

Any chance that column A is empty on any of those rows?

If it is, then:
destination:=Sheets(oCell.Value).Range("A65536").End(xlUp).Offset(1, 0)
is going to have trouble.

You may be overwriting the same row again and again and again.

Maybe you could use column B--since you know it's populated:

destination:=Sheets(oCell.Value).Range("B65536").End(xlUp).Offset(1, -1)