Excel 2010 VBA - How to optimize this code so that it does not lag?

Question

I am new to VBA and recently I created some macros. I currently have one that works, but sometimes it’s not very cooperative. I did a bunch of reading on how to optimize VBA code, but I'm still not very far away. I understand that use Selectis bad, and I deleted as many lines Selectas I could. I also read that many if statementsin combination with loopscan also be launched (of course, I have a multiplicity of both).

So, I know some reasons why my code is bad, but I really don't know how to fix it. I added

    Application.ScreenUpdating = False
    Application.ScreenUpdating = True

for my macro. It helped, but not so much. I have other macros that can work for a long time and never freeze. This macro freezes if it does not complete in 10-15 seconds. If I only have a couple of 100 rows of data, this is not a problem. If I have several 1000 rows of data, they do not end before they freeze.

Option Explicit

Sub FillGainerPrices()

    Application.ScreenUpdating = False
    'Search each name on "Gainer Prices" and if the same name is on "Gainers", but not on Gainer Prices _
move it over to Gainer Prices tab.  Then call Historical Query and Fill Names

Dim LastRow1 As Long
LastRow1 = Sheets("Gainers").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Dim LastRow2 As Long
LastRow2 = Sheets("Gainer Prices").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Dim Name1 As Range
Dim Name2 As Range
For Each Name1 In Sheets("Gainers").Range("B2:B" & LastRow1)
    Set Name2 = Sheets("Gainer Prices").Range("A2:A" & LastRow2).Find(Name1, LookIn:=xlValues, LookAt:=xlWhole)
    If Name2 Is Nothing Then
        If Name1.Offset(0, -1) < Date - 15 Then
            Name1.Copy
            Sheets("Gainer Prices").Select
            Range("C" & Cells.Rows.Count).End(xlUp).Offset(1, -2).Select
            ActiveSheet.Paste
            Call HistoricalQuery
        End If
    End If
Next Name1
Application.ScreenUpdating = True

'Fill in Names and remaining symbols here
Call FillNamesAndSymbols

End Sub

Call HistoricalQueryand it’s Call FillNamesAndSybmolspretty fast and doesn’t seem to have a problem when I run them myself, so I don’t think they are causing the problem. I suppose the problem is finding a single name 1000 times and then copying and pasting over and over again, but I cannot figure out how to get rid of the copy and paste the part without a macro giving me the wrong results.

- , . , , , , , . , . - , . !

Answer 1

.

Improvments:

• : : 0.8828125 , : 10.003 . ( 1000 )
• : arr = Sheets("Gainer Prices").Range("A2:A" & LastRow2).Value -
• Application.Match Range.Find - .
• Range(..).Value = Range(..).Value copy/paste
• select/active

---

Sub FillGainerPrices()
    Dim LastRow1 As Long
    Dim LastRow2 As Long
    Dim Lastrow3 As Long

    Dim Name1 As Range

    Dim sh1 As Worksheet
    Dim sh2 As Worksheet

    Dim arr As Variant
    'remember start time
    Dim start as Long
    start = Timer

    Application.ScreenUpdating = False

    Set sh1 = ThisWorkbook.Sheets("Gainers")
    Set sh2 = ThisWorkbook.Sheets("Gainer Prices")

    With sh1
        LastRow1 = .Cells(.Rows.Count, "B").End(xlUp).Row
    End With
    With sh2
        LastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row  
        arr = .Range("A2:A" & LastRow2).Value          
    End With

    For Each Name1 In sh1.Range("B2:B" & LastRow1)
        If IsError(Application.Match(Name1.Value, arr, 0)) Then
            If Name1.Offset(0, -1) < Date - 15 Then
                With sh2
                    Lastrow3 = .Cells(.Rows.Count, "C").End(xlUp).Row
                    .Range("A" & Lastrow3 + 1).Value = Name1.Value
                End With

                Call HistoricalQuery
            End If
        End If
    Next Name1

    'Fill in Names and remaining symbols here
    Call FillNamesAndSymbols

    Application.ScreenUpdating = True
    'To see timing result press CTRL+G in the VBE window, or change Debug.Print to MsgBox
    Debug.Print "Code evaluates for: " & Timer - start
End Sub

Answer 2

Name1.Copy
Sheets("Gainer Prices").Select
Range("C" & Cells.Rows.Count).End(xlUp).Offset(1, -2).Select
ActiveSheet.Paste

- :

Name1.copy destination:=Sheets("Gainer Prices").Range("C" & Cells.Rows.Count).End(xlUp).Offset(1, -2)

, ,

Sheets("Gainer Prices").Range("C" & Cells.Rows.Count).End(xlUp).Offset(1, -2).value=Name1.value