On Mar 9, 9:41 am, Dave Peterson wrote:
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)

Thanks Dave, that's perfect!

And thanks to everyone else who has helped .

cheers

Damien