vba copy data every 10 minutes

ste33uka

Active Member
Joined
Jan 31, 2020
Messages
358
Office Version
  1. 2019
Platform
  1. Windows
I use the follwing vba to auto copy data from cells to cells every ten 10 minutes.
It runs for 70 sheets.
i have just added the 1st 2 sheets as an example.

Would there be anyway to make it copy faster ?
Would adding code in vba "if cell has value over >0" make it faster? , as some cells dont have data all the time.
Would anyone have an example how to add " if cell is over >0" ?
Thanks

VBA Code:
Sub copytenminutes()
     Sheet1.Range("cd1").Value = Sheet1.Range("F5").Value
     Sheet1.Range("ce1").Value = Sheet1.Range("f6").Value
     Sheet1.Range("cf1").Value = Sheet1.Range("F7").Value
     Sheet1.Range("cg1").Value = Sheet1.Range("f8").Value
     Sheet1.Range("ch1").Value = Sheet1.Range("F9").Value
     Sheet1.Range("ci1").Value = Sheet1.Range("f10").Value
     Sheet1.Range("cj1").Value = Sheet1.Range("F11").Value
     Sheet1.Range("ck1").Value = Sheet1.Range("f12").Value
     Sheet1.Range("cl1").Value = Sheet1.Range("F13").Value
     Sheet1.Range("cm1").Value = Sheet1.Range("f14").Value
     Sheet1.Range("cn1").Value = Sheet1.Range("F15").Value
     Sheet1.Range("co1").Value = Sheet1.Range("f16").Value
     Sheet1.Range("cp1").Value = Sheet1.Range("F17").Value
     Sheet1.Range("cq1").Value = Sheet1.Range("f18").Value
     Sheet1.Range("cr1").Value = Sheet1.Range("F19").Value
    
     Sheet2.Range("cd1").Value = Sheet2.Range("F5").Value
     Sheet2.Range("ce1").Value = Sheet2.Range("f6").Value
     Sheet2.Range("cf1").Value = Sheet2.Range("F7").Value
     Sheet2.Range("cg1").Value = Sheet2.Range("f8").Value
     Sheet2.Range("ch1").Value = Sheet2.Range("F9").Value
     Sheet2.Range("ci1").Value = Sheet2.Range("f10").Value
     Sheet2.Range("cj1").Value = Sheet2.Range("F11").Value
     Sheet2.Range("ck1").Value = Sheet2.Range("f12").Value
     Sheet2.Range("cl1").Value = Sheet2.Range("F13").Value
     Sheet2.Range("cm1").Value = Sheet2.Range("f14").Value
     Sheet2.Range("cn1").Value = Sheet2.Range("F15").Value
     Sheet2.Range("co1").Value = Sheet2.Range("f16").Value
     Sheet2.Range("cp1").Value = Sheet2.Range("F17").Value
     Sheet2.Range("cq1").Value = Sheet2.Range("f18").Value
     Sheet2.Range("cr1").Value = Sheet2.Range("F19").Value

     Application.OnTime Now + TimeValue("00:10:00"), "copytenminutes"


End Sub
 

ste33uka

Active Member
Joined
Jan 31, 2020
Messages
358
Office Version
  1. 2019
Platform
  1. Windows
i get error = cant execute code in the break mode ?
edit seems to be working now
Thanks alot rollis, appreciate it.

VBA Code:
Sub threemin()

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
  

With Sheet1
    lr = .Range("CD" & .Rows.Count).End(xlUp).Row + 1 'last used row +1
    .Range("CD" & lr & ":CR" & lr).Value = Application.Transpose(.Range("F5:F19").Value)
End With

With Sheet2
    lr = .Range("CD" & .Rows.Count).End(xlUp).Row + 1 'last used row +1
    .Range("CD" & lr & ":CR" & lr).Value = Application.Transpose(.Range("F5:F19").Value)
End With




  
    Application.OnTime Now + TimeValue("00:3:00"), "threemin"

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

rollis13

Active Member
Joined
Jul 30, 2012
Messages
414
Office Version
  1. 2016
Platform
  1. Windows
Since you have 70 sheets to update, did you consider my suggestion in post #4 ?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,001
Messages
5,622,126
Members
415,878
Latest member
jjj12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top