Subscript out of range error
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).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
|