Every 1 hour data should copy paste from A to B, then A to C and so on

Try application.Ontime to run sub after 1 hour
LIke this:

PHP Code: 

Sub Timer()
application.ontime Now + timevalue("01:00:00"), "MainSub"
End sub
'----------------------
Sub MainSub()
<Your main code here>
End sub 


Could you post a image/screenshot of sample worksheet?
I dont know how to define the last column to paste column A into after each hour. Are there any data in other column to the right. i.e, AA or AB,...?

Invalid Attachment.
Try again.

Please attach the workbook, NOT a picture of it!

Maybe below code pasted into module

PHP Code: 

Option Explicit
Public t As Long
' set timer
Sub timer()
t = Hour(TimeValue(Now)) + 1
' set next run time to next hour from now, stop at 17h
Application.OnTime Date + t / 24, "copyRng", Date + 17 / 24
End Sub
Sub copyRng()
Dim lr&, copyRng As Range, pasteRng As Range
lr = Cells(Rows.Count, "A").End(xlUp).Row
t = Hour(TimeValue(Now)) 'current hour
Set copyRng = Range("A6:A" & lr)
On Error Resume Next
Set pasteRng = Range("A6").Offset(0, t - 6)
Range("A6:A" & lr).copy pasteRng
pasteRng.EntireColumn.AutoFit
On Error GoTo 0
End Sub 


Could you give a sample with minute?

Try again:

PHP Code: 

Option Explicit
Public t As Double
' set timer
Sub timer()
t = WorksheetFunction.Ceiling(Minute(TimeValue(Now)), 15) 'current minute (in whole number)round upto next 15
' set next run time to next hour from now, stop at 17h
Application.OnTime Date + Hour(TimeValue(Now)) / 24 + t / 1440, "copyRng", Date + 17 / 24
End Sub

'------------------
Sub copyRng()
Dim lr&, copyRng As Range, pasteRng As Range
lr = Cells(Rows.Count, "A").End(xlUp).Row
With WorksheetFunction
    t = .Ceiling(TimeValue(Now), 15 / 1440) 'current minute round upto next 15'
    Set copyRng = Range("A6:A" & lr)
    On Error Resume Next
    Set pasteRng = Range("A6").Offset(0, .Match(t, Range("C5:AM5")))
    Range("A6:A" & lr).Copy pasteRng
    pasteRng.EntireColumn.AutoFit
    On Error GoTo 0
End With
End Sub 


Please upload newest file with code, to test what 'd happened

I am calling around for help. hope somebody reply soon.

See if this helps: https://www.myonlinetraininghub.com/...ication-ontime

You need to set the scheduled time to a variable so that a) you can increment it and b) so you can stop the timer loop.

i can't post the code here (and don't know why)

Hi,

Thanks again Bsalv and Bebo.
Previously, the VBA code had to be pressed every 15 minutes to run. But now when I open the file the timer doesn't start. The timer starts only when I have to run the VBA code.

Please check the uploaded file if the formula is added in the A column then the pasting source is changed automatically.

hello,
add an "open"-event in thisworkbook

Please Login or Register  to view this content.

hello,
Do you want to copy the data from the "Source" or from the B-column (which are the same ?)

hello.
see attachment

your point 1 = this correction
If Hour(t) < 8 Then t = Date + TimeSerial(8, 0, 1) 'before 8 AM
If Format(t, "hh:mm") > "17:00" Then t = Date + 1 + TimeSerial(8, 0, 1) 'after 5PM

your point 2 = this correction
With c(2, col + 1).Resize(20, 40) 'everything to the right of the actual column is empty with this
.ClearContents
.Interior.Color = xlNone
.EntireColumn.ColumnWidth = 5
End With