VBA script to copy sheet with in document at specific time

Hey all,

Relatively new to scripting in VBA. I am trying to write a script that will run at 4:00pm each day. What it will do is clone a master sheet and rename the clone to the current date. What I have right now is not right currently and I know that, so i was going to see if anyone out there could support me in writing and understanding this script I'm trying to write.

Here is what I have so far


Here is the script that needs to run at 4:00pm each day

Sub copyWS()

Set ws = Sheets("Master Sheet")
ws.Select
ws.Copy after:=Sheets(2)
Set ws = Sheets("Master Sheet (2)")
ws.Name = Format(Date, "mm-dd-YY")
Worksheets("Master Sheet").Range("A2:A500").ClearContents
ActiveWorkbook.Save

End Sub


And here is my start at the process for running it each day



Private Sub CheckTime()

ScheduledTime = Now + TimeSerial(0, 15, 0)
Application.OnTime TimeValue("16:00:00"), , TimeValue("23:59:00")



End Sub


My biggest confusion is what to insert here
Application.OnTime TimeValue("16:00:00"), #########, TimeValue("23:59:00")

After Clicking around for a a while longer i have worked towards this. It is still not all the way there so if anyone has any pointer.

Sub copyWS()
Dim CurrentTime As String

CurrentTime = Time

If CurrentTime > ("16:00:00") Then
Set ws = Sheets("Master Sheet")
ws.Select
ws.Copy after:=Sheets(2)
Set ws = Sheets("Master Sheet (2)")
ws.Name = Format(Date, "mm-dd-YY")
Worksheets("Master Sheet").Range("A2:A500").ClearContents
Sheets("Master Sheet").Activate
ActiveSheet.Range("A2").Select
ActiveWorkbook.Save
Else
Application.OnTime Now + TimeSerial(0, 5, 0), "copyWS"
End If
End Sub

Hello,

Step 1
Put a line of code in your workbooks Open event (Workbook_Open) that says this:

Application.OnTime TimeValue("16:00:00"), "CopySheet"

That is 24 hour format time so that would be 4:00 Pm.

Step 2
Add a module and put a macro in it named CopySheet ie..

Sub CopySheet()

'Whatever code you want to run at 4:00 pm here

End Sub

When you open the workbook it will initiate the OnTime command to run the macro named 'CopySheet' at 4:00 pm
If you don't want that to happen when you open the workbook, then just put that line of code in whatever event you want to happen in, or a button or whatever you want.

Hope that helps.

So this could be your code in your module (Keep in mind you can only run this once each day unless you delete the new sheet it creates because it tries to rename a new sheet to the same name as the last time you ran it which is the date of that day).. also you don't need to activate sheets to copy them.

Sub CopySheet()
Dim ws As Worksheet


Set ws = Sheets("Master Sheet")
ws.Copy after:=Sheets(2)
ActiveSheet.Name = Format(Date, "mm-dd-YY")

Worksheets("Master Sheet").Range("A2:A500").ClearContents
Sheets("Master Sheet").Activate
ActiveSheet.Range("A2").Select
ActiveWorkbook.Save


End Sub