Copy paste of data from one worksheet to other using 2 ranges based on a condition.... Pls help!

[ajaymi]

With Below code : I have been getting an error

Runtime :6 "overflow.

it works till 18th rows absolutely in the manner I wanted. post that ... i goes to debug.
Please help.



Sub daily()
Dim i, Y, x As Long
Dim ws1 As Worksheet: Set ws1 = ActiveWorkbook.Sheets("RAW")
Dim ws2 As Worksheet: Set ws2 = ActiveWorkbook.Sheets("Report wise elapse summary")
Dim Ary1 As Range
Dim ary2 As Range

For x = 6 To 10000

Y = ws2.Cells(2, 3)
For i = 2 To 10000:

If ws1.Cells(i, 10) = Y Then
ws1.Activate
Set Ary1 = Range(Cells(i, 3), Cells(i, 13))
ws2.Activate
Set ary2 = Range(Cells(x, 2), Cells(x, 12))
ary2.Value = Ary1.Value


x = x + 1

End If
Next i
Next x

End Sub

 

[JLGWhiz]

x = x + 1

This method of incrementing the value of x could also be a problem, because you have it in the inner loop and it will increment to 10,000 right along with the value of i. Then when the outer loop automatically increments x is starting at 10, 001 for the second iteration of the outer loop, 19,994 for the third iteration of the outer loop and right on up to almost 100 million by the time the entir code runs.
Try this modified version an see if it still errors.

Sub daily()
Dim i As Long, Y
Dim ws1 As Worksheet: Set ws1 = ActiveWorkbook.Sheets("RAW")
Dim ws2 As Worksheet: Set ws2 = ActiveWorkbook.Sheets("Report wise elapse summary")
Dim Ary1 As Range
Dim ary2 As Range 
Y = ws2.Cells(2, 3).Value
    For i = 2 To 10000
       If ws1.Cells(i, 10) = Y Then
           With.ws1
               Set Ary1 = Range(.Cells(i, 3), .Cells(i, 13))
           End With
           With.ws2
               Set ary2 = Range(.Cells(i + 4, 2), .Cells(i + 4, 12))
           End With
           ary2.Value = Ary1.Value    
       End If
       Set Ary1 = Nothing
       Set Ary2 = Nothing
    Next i
End Sub