Subscript out of range error

[Damien McBain]

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").End(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

 

[Don Guillett]

I'm confused about your syntax but the basic idea is that you tried to paste
without selecting. So, just shortcut it by this one liner. BTW you can
usually break up a line with a space and underscore

sourcerange.copy destinationrange

or
sourcerange.copy _
destinationrange

 

[Jean-Yves]

Damien,

Do you have some rows already on worksheet(rt).
Else go go down to the very last row,65 536, and paste one row below which
is impossible.

In that case, use Range("A65000").End(xlUp).Offset(1,0)).
Please also note that you only have to specify the upper left cell of the
destination range and that
"Offset(1, 9)"does not resize the range.
See ammended line
Regards
JY

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).Offset(1, 0),
Range("A650001").End(xlUp).Offset(1,0)).Paste '
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Else
End If

Next rcd

Next rt

Regards
JY

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").End(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

 

[Damien McBain]

Damien,

Do you have some rows already on worksheet(rt).
Else go go down to the very last row,65 536, and paste one row below which
is impossible.

In that case, use Range("A65000").End(xlUp).Offset(1,0)).
Please also note that you only have to specify the upper left cell of the
destination range and that
"Offset(1, 9)"does not resize the range.
See ammended line
Regards
JY

Thanks JY & Don.

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(xlUp).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).Offset(1, 0),
Range("A650001").End(xlUp).Offset(1,0)).Paste '
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Else
End If

Next rcd

Next rt

Regards
JY

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").End(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]

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(xlUp).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("A65536").End(xlUp).Offset(1, 0)



Damien McBain wrote:

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(xlUp).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).Offset(1, 0),
Range("A650001").End(xlUp).Offset(1,0)).Paste '
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Else
End If

Next rcd

Next rt

Regards
JY

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").End(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

 

[damien]

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(xlUp).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("A65536").End(xlUp).Offset(1, 0)

Thanks Dave, that's perfect!

And thanks to everyone else who has helped .

cheers

Damien