Alternative method fo simple function?

[jc0r]

Hi all, i have the below piece of code that i run every 200 milliseconds. It works fine however i am looking for a better, more efficient way of producing the same result. I use AutoHotKey to get information from Excel and quite often i will get a Callee error, i assume this is because Excel is in Edit mode whilst the routine is in operation?

Any ideas please.

Many thanks

Sub PriceRefresh()
Worksheets("Data").Range("AJ5:AJ11").Value = Worksheets("Data").Range("AI5:AI11").Value
Worksheets("Data").Range("AI5:AI11").Value = Worksheets("Data").Range("AH5:AH11").Value
Worksheets("Data").Range("AH5:AH11").Value = Worksheets("Data").Range("AG5:AG11").Value
Worksheets("Data").Range("AG5:AG11").Value = Worksheets("Data").Range("AF5:AF11").Value
Worksheets("Data").Range("AF5:AF11").Value = Worksheets("Data").Range("AE5:AE11").Value
Worksheets("Data").Range("AE5:AE11").Value = Worksheets("Data").Range("AD5:AD11").Value
Worksheets("Data").Range("AD5:AD11").Value = Worksheets("Data").Range("AC5:AC11").Value
Worksheets("Data").Range("AC5:AC11").Value = Worksheets("Data").Range("AB5:AB11").Value
Worksheets("Data").Range("AB5:AB11").Value = Worksheets("Data").Range("AA5:AA11").Value
Worksheets("Data").Range("AA5:AB11").Value = Worksheets("Data").Range("F5:F11").Value
Worksheets("Data").Range("AU5:AU11").Value = Worksheets("Data").Range("AT5:AT11").Value
Worksheets("Data").Range("AT5:AT11").Value = Worksheets("Data").Range("AS5:AS11").Value
Worksheets("Data").Range("AS5:AS11").Value = Worksheets("Data").Range("AR5:AR11").Value
Worksheets("Data").Range("AR5:AR11").Value = Worksheets("Data").Range("AQ5:AQ11").Value
Worksheets("Data").Range("AQ5:AQ11").Value = Worksheets("Data").Range("AP5:AP11").Value
Worksheets("Data").Range("AP5:AP11").Value = Worksheets("Data").Range("AO5:AO11").Value
Worksheets("Data").Range("AO5:AO11").Value = Worksheets("Data").Range("AN5:AN11").Value
Worksheets("Data").Range("AN5:AN11").Value = Worksheets("Data").Range("AM5:AM11").Value
Worksheets("Data").Range("AM5:AM11").Value = Worksheets("Data").Range("AL5:AL11").Value
Worksheets("Data").Range("AL5:AL11").Value = Worksheets("Data").Range("H5:H11").Value
End Sub

 

[James006]

Hello,

Most probably Worksheets("Data") is your active sheet ...so you can delete it

Your copies will gain in speed with Application.Calculation = =xlCalculationManual

Hope this will help

 

[Norie]

The code could probably be tidied up/shortened by introducing a loop or two but I don't think that would help with any potential error.

What makes you think the error you mention is caused by Excel being in Edit mode?

If Excel is in Edit mode code shouldn't run.

 

[Peter_SSs]

Sub PriceRefresh()
Worksheets("Data").Range("AJ5:AJ11").Value = Worksheets("Data").Range("AI5:AI11").Value
Worksheets("Data").Range("AI5:AI11").Value = Worksheets("Data").Range("AH5:AH11").Value
Worksheets("Data").Range("AH5:AH11").Value = Worksheets("Data").Range("AG5:AG11").Value
Worksheets("Data").Range("AG5:AG11").Value = Worksheets("Data").Range("AF5:AF11").Value
Worksheets("Data").Range("AF5:AF11").Value = Worksheets("Data").Range("AE5:AE11").Value
Worksheets("Data").Range("AE5:AE11").Value = Worksheets("Data").Range("AD5:AD11").Value
Worksheets("Data").Range("AD5:AD11").Value = Worksheets("Data").Range("AC5:AC11").Value
Worksheets("Data").Range("AC5:AC11").Value = Worksheets("Data").Range("AB5:AB11").Value
Worksheets("Data").Range("AB5:AB11").Value = Worksheets("Data").Range("AA5:AA11").Value
Worksheets("Data").Range("AA5:A[COLOR="#FF0000"][B][SIZE=+1]B[/SIZE][/B][/COLOR]11").Value = Worksheets("Data").Range("F5:F11").Value
Worksheets("Data").Range("AU5:AU11").Value = Worksheets("Data").Range("AT5:AT11").Value
Worksheets("Data").Range("AT5:AT11").Value = Worksheets("Data").Range("AS5:AS11").Value
Worksheets("Data").Range("AS5:AS11").Value = Worksheets("Data").Range("AR5:AR11").Value
Worksheets("Data").Range("AR5:AR11").Value = Worksheets("Data").Range("AQ5:AQ11").Value
Worksheets("Data").Range("AQ5:AQ11").Value = Worksheets("Data").Range("AP5:AP11").Value
Worksheets("Data").Range("AP5:AP11").Value = Worksheets("Data").Range("AO5:AO11").Value
Worksheets("Data").Range("AO5:AO11").Value = Worksheets("Data").Range("AN5:AN11").Value
Worksheets("Data").Range("AN5:AN11").Value = Worksheets("Data").Range("AM5:AM11").Value
Worksheets("Data").Range("AM5:AM11").Value = Worksheets("Data").Range("AL5:AL11").Value
Worksheets("Data").Range("AL5:AL11").Value = Worksheets("Data").Range("H5:H11").Value
End Sub

I'm assuming that the red B should actually be an A, otherwise you are over-wring AB5:AB11 twice.

This would be a little more efficient.

Sub PriceRefresh_New()
  With Worksheets("Data")
    .Range("AB5:AJ11").Value = .Range("AA5:AI11").Value
    .Range("AA5:AA11").Value = .Range("F5:F11").Value
    .Range("AM5:AU11").Value = .Range("AL5:AT11").Value
    .Range("AL5:AL11").Value = .Range("H5:H11").Value
  End With
End Sub

 

[jc0r]

Wow! Many thanks for your suggestions.

Norie, i am just assuming at this stage that the error is because of my VBA script as there is not anything else contained in the Excel VBA Workbook. On open it just runs that code every 200 milliseconds. I'm not sure if anyone is familiar with Auto Hotkey but the error i recieve is the following:

Error: 0x80010001 - Call was rejected by callee.

Specifically: Sheets

Line#
---> 026: SelecID := XL.Sheets("Selection".Range("B5").Text

Continue running the script?

Peter_SSs you are indeed correct, that "B" is an error, i will make the change. Although your code is a lot nicer, i am not sure it will achieve what i am after. You see, the function of the code is to create 2 ranges that both contain 10 columns. Working right, each column is the previous columns data then i can draw an average of the data over the last 2 seconds

 

[jc0r]

Can anyone add anything to this please? Either the error im recieving or a better way to write my script? Many thanks