Can someone please help me fix this code I want the macro to start at 7am and run every 15

Try this.

Public NextTime As Date
'Place this declaration at the very top of the code module
    
Sub Timed_Copy_Start()
    'Start the Timer
    If Time < TimeValue("07:00:00") Then
        NextTime = TimeValue("07:00:00")    'Start at 7 AM
        Application.OnTime NextTime, "Timed_Copy_Values"
    ElseIf Time < TimeValue("18:00:00") Then
        Timed_Copy_Values   'Start now
    Else
        MsgBox "It's after 7 PM.", , "Timed Copy Canceled"
    End If
End Sub
    
Private Sub Timed_Copy_Values()
    
    'Copy data
    Sheets("DATA").Range("E2:E5").Copy _
        Destination:=Sheets("EWFM").Range("E" & Rows.Count).End(xlUp).Offset(1)
    
    If Time < TimeValue("18:00:00") Then
        'Schedule next time copy
        NextTime = Now + TimeValue("00:15:00")
        Application.OnTime NextTime, "Timed_Copy_Values"
    Else
        'save and close
        ThisWorkbook.Close SaveChanges:=True
    End If
        
End Sub
    
Sub Timed_Copy_Quit()
    'This just stops the OnTime timer, but doesn't save and close
    On Error Resume Next
    Application.OnTime NextTime, "Timed_Copy_Values", Schedule:=False
End Sub

Run Timed_Copy_Start to start the timer. If it's started before 7 AM, the first copy will be at 7 AM. If it's started after 7 AM. It will copy immediately and then every 15 minutes after.

Run Timed_Copy_Quit if you ever want to stop the OnTime events manually. If you didn't do this and just closed the workbook but left Excel open, the workbook would reopen and run the OnTime event.

It seems to be working but its coping the formula from the main cell, i need it to copy just the values ( just the numbers), i have also noticed that it sometime it copies the data 3-4 time in a 15 min run, is this issue because of the my excel.

Quick question Do i need to leave Excel Open for this to run on time
Thank you and really appreciate your help

Originally Posted by nareenrv

It seems to be working but its coping the formula from the main cell, i need it to copy just the values ( just the numbers),

Change this...

    'Copy data
    Sheets("DATA").Range("E2:E5").Copy _
        Destination:=Sheets("EWFM").Range("E" & Rows.Count).End(xlUp).Offset(1)

To this...

    'Copy data
    Sheets("EWFM").Range("E" & Rows.Count).End(xlUp).Offset(1).Resize(4).Value = _
       Sheets("DATA").Range("E2:E5").Value

i have also noticed that it sometime it copies the data 3-4 time in a 15 min run, is this issue because of the my excel.

I'm not sure why that would happen.

Quick question Do i need to leave Excel Open for this to run on time

Yes

Thank you and really appreciate your help

You're welcome.