1

This seems to be a common aim but I cannot find anyone else having the same issue as me. I am looking to record prices from an exchange, the prices feed in from the exchange into an excel document and updates automatically.

I have written the below code to copy and paste from the lookups (simply vlookups) that filter the source data. When this runs it does not copy the new values that are in the look ups it continues to use the original values that it saw on its first loop, duplicating the information on each new line. It seems like it gets stuck on the original data and does not refresh on each iteration of the loop.

Would anyone be able to advise on where I am missing something?

Sub Copying()

Dim lRow As Long

For lRow = 2 To 13000
    ActiveWorkbook.RefreshAll
    Application.CutCopyMode = False
    Sheets("Bet Angel").Calculate
    Sheets("Data Pull").Calculate
    Sheets("Data Pull").Select
    Range("A2:Q2").Select
    Selection.Copy
    Sheets("Data").Select
    Range("A" & lRow).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
    Application.Wait (Now + TimeValue("0:00:01"))
    Sheets("Data").Select
    Range("A" & lRow).Select
    Application.CutCopyMode = False
Next lRow

End Sub

mgae2m
  • 1,134
  • 1
  • 14
  • 41
Leon
  • 11
  • 2
  • What is `.Calculate`doing? You copy range("A2:Q2") from`Data Pull`and paste it into`Data`. If `.Calculation` don't changes the data in the range, you always copy the same data.` Don use`.Select`/ `.Selection`, see [how-to-avoid-using-select-in-excel-vba](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – BitAccesser Oct 01 '17 at 14:49
  • You estimated execution time? 12999 iterations with 1 second wait + other code execution) > 12999 seconds, more than 3.5 hours! – BitAccesser Oct 01 '17 at 14:54

1 Answers1

0

Here the cleaned up function without the macro recorder fragments: 

Sub Copying()

Dim lRow As Long

With ThisWorkbook  'reference to workbook
    For lRow = 2 To 13000
        .RefreshAll  'Maybe useless, are there backgroundqueries to refresh on every iteration?
        'Application.CutCopyMode = False  'useless fragment from marco recorder, like .Select/.Selected
        .Sheets("Bet Angel").Calculate
        .Sheets("Data Pull").Calculate
        .Sheets("Data Pull").Range("A2:Q2").Copy .Sheets("Data").Range("A" & lRow)  ' copy by code not by simulating useraction
        'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
        'Application.Wait (Now + TimeValue("0:00:01")) 'useless without .PasteSpecial unless you need wasteing time
        ''Sheets(2).Select 'useless
        'Range("A" & lRow).Select  'useless
        'Application.CutCopyMode = False  'useless
    Next lRow
End With

End Sub

This function won't solve the issue with the not updated data, but copies lighting fast, compared to your old code;-)

Comment on this answer to make the data refresh work.

Why Application.CutCopyMode is useless.

BitAccesser
  • 719
  • 4
  • 14