Run-Time error '7' Out of Memory

Hi!

I am very VERY new to VBA macros. After spending a few days trying to put together logic by looking at different websites and codes, I finally thought I had something that would work.... however, I went to run the code and I received a run time error I cannot figure this one out... please help! here is my code:


Sub CDW()

Dim wb As Worksheet: Set wb = ActiveWorkbook.Sheets("Routing Guide Overview")
Dim wb2 As Worksheet: Set wb2 = ActiveWorkbook.Sheets("PRINTABLE Routing Guide")
Dim ToFind As String
Dim i, x As Long
Dim Ary1 As Range
Dim Ary2 As Range
Dim size As String


Application.ScreenUpdating = False

i = 2
x = 12
size = "20FT"
ToFind = "CDW"


Do
i = i + 1
For i = 2 To 1000:
If wb.Cells(i, 2) = ToFind And wb.Cells(i, 3) = size Then
wb.Activate
Set Ary1 = Range("Fi:Ii")
wb2.Activate
Set Ary2 = Range("Ax:Dx")
Ary2.Value = Ary1.Value
x = x + 1
End If
Next i

Loop Until Cells(i, 2).Value = ""

End Sub

Too much loop. What are you trying to accomplish, there is probably a more efficient way.

Ok

I want to loop through column B in the "Routing Guide Overview" to look for the term CDW (there are multiple). For each time it finds CDW, I want it to look at column C to ensure it lists "20FT". If both of those values are true, then I would like to copy the corresponding cells in columns F through I on the "Routing Guide Overview" on to columns A through D on the sheet titled "PRINTABLE Routing Guide"

For reference there are currently 500 rows on "Routing Guide Overview" it would need to loop through.

Thank you for the help!!

Good Explanation. Try this:

Please Login or Register  to view this content.

OMG Fantastic!!!!

Now if I can be picky.. is there a way to only paste special values? Also I forgot to add I need the paste to start on A12 not A1 of "PRINTABLE Routing Guide".

you are awesome!! you saved me a lot of headache!!

Updated:

Please Login or Register  to view this content.

*If the supplied code works for you I would be more than happy to receive rep points (bottom left)

This is fantastic!! Thank you!

And yes I would be more than happy to give you as many rep points as possible. thank you for the help!