Subscript out of range means that there's some sort of collection that doesn't
have element by the name/index you're using.

In this line:

Range(rcd, rcd.End(xlToRight)).Copy _
Destination:=Worksheets(rt).Range("A65536").End(xl Up).Offset(1, 0)

The only thing I see that could cause that error is worksheets(rt).

If rt is a number (say 1234), then worksheets(1234) means the 1234th worksheet
from the left.

You could use this to make sure you're using the name of the sheet--not the
index:

Range(rcd, rcd.End(xlToRight)).Copy _
Destination:=Worksheets(cstr(rt)).Range("A65536"). End(xlUp).Offset(1, 0)

cstr(rt) (I'd use cstr(rt.value) myself) will convert that number to a
string--so it would be like typing:

worksheets("1234").....

And I'd make another change, too. I'd qualify the range to copy:

worksheets("data").Range(rcd, rcd.End(xlToRight)).Copy _
Destination:=Worksheets(cstr(rt.value)).Range("A65 536").End(xlUp).Offset(1, 0)



Damien McBain wrote:
<<snipped

I now have this (which still doesn't work!) It still produces the subscript
out of range error on the "copy" line.
==========================
Sub ProcessData()

For Each rt In Worksheets("Routes").Range("A2",
Worksheets("Routes").Range("A2").End(xlDown))

With Worksheets.Add
.Name = rt
.Range("A1") = "Route"
End With
For Each rcd In Worksheets("Data").Range("A2",
Worksheets("Data").Range("A2").End(xlDown))
If rcd.Value = rt.Value Then
Range(rcd, rcd.End(xlToRight)).Copy _
Destination:=Worksheets(rt).Range("A65536").End(xl Up).Offset(1, 0)

Else
End If

Next rcd

Next rt
End Sub
=======================
grrr...


For Each rt In Worksheets("Routes").Range("A2",
Worksheets("Routes").Range("A2").End(xlDown))

With Worksheets.Add
.Name = rt
End With
For Each rcd In Worksheets("Data").Range("A2",
Worksheets("Data").Range("A2").End(xlDown))
If rcd.Value = rt.Value Then
Range(rcd, rcd.End(xlToRight)).Copy
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXx
Worksheets(rt).Range(Range("A1").End(xlDown).Offse t(1, 0),
Range("A650001").End(xlUp).Offset(1,0)).Paste '
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxx
Else
End If

Next rcd

Next rt

Regards
JY

"Damien McBain" wrote in message
...
This code returns a "Subscript out of range error at the marked line (it's
1 line in the code but wrapped in this post). The "copy" method works
because the correct range is copied but the error occurs on the line with
the paste method:
=======================
Sub ProcessData()

For Each rt In Worksheets("Routes").Range("A2",
Worksheets("Routes").Range("A2").End(xlDown))

With Worksheets.Add
.Name = rt
End With
For Each rcd In Worksheets("Data").Range("A2",
Worksheets("Data").Range("A2").End(xlDown))
If rcd.Value = rt.Value Then
Range(rcd, rcd.End(xlToRight)).Copy

Worksheets(rt).Worksheets(rt).Range(Range("A1").En d(xlDown).Offset(1, 0),
Range("A1").End(xlDown).Offset(1, 9)).Paste '<<<---- problem line
Else
End If

Next rcd

Next rt
End Sub
===========================

I have also tried pasting to a 1 cell range
[Worksheets(rt).Range("A1").End(xlDown).Offset(1, 0)] which also generates
an error.

The data which is copied is A2:I2 (1 row x 9 columns)

Any ideas? So close... :/

cheers

Damien

--

Dave Peterson