I am new to VBA, and I'm trying to write the code that I've researched and tested on a basic workbook. I may have things out of order, and I'm not sure if this should live in a module/seperate modules, or in thisworkbook section. This workbook will stay open all the time.
I am trying to have it do 2 things.
1- At 6:30 AM and 6:30 PM run macro "RunOnTime"
2- At 6:25 AM and 6:25 PM run macro "EndOfShift"
purpose of RunOnTime is to Calculate (F9) the sheet so it will look at an external source, and current data in to the spreadsheet.
Purpose of EndOfShift is to copy the data at the end of the shift, and paste values into a database.
This is the code I've put together
[Public dTime As Date
Dim lNum As Long
Sub RunOnTime()
dTime = Now + TimeSerial(0, 0, 10)
Application.OnTime dTime, "RunOnTime"
lNum = lNum + 1
If lNum = 47 Then
Run "CancelOnTime"
Else
Sub Update()
' This will update the data from IP.21
Calculate
End Sub
End If
End Sub
Sub CancelOnTime()
Application.OnTime dTime, "RunOnTime", , False
End Sub
Private Sub Day_Start()
'Runs a refresh of IP.21 data ever 15 minutes starting At 6:30 AM
Application.OnTime TimeValue("6:30:00"), "RunOnTime"
End Sub
Private Sub Night_Start()
'Runs a refresh of IP.21 data ever X minutes starting At 6:30 PM
Application.OnTime TimeValue("18:30:00"), "RunOnTime"
End Sub
Private Sub Night_End()
'Runs End of Shift Data Capture at 6:25 AM
Application.OnTime TimeValue("6:25:00"), "EndOfShift"
End Sub
Private Sub Day_End()
'Runs End of Shift Data Capture at 6:25 PM
Application.OnTime TimeValue("18:25:00"), "EndOfShift"
End Sub
Sub EndOfShift()
' EndOfShift Macro
' This macro captures the data at the end of the shift and records it for future use
'
Sheets("Data").Select
Rows("11:11").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A9").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("A11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Today").Select
Range("A2").Select
Application.CutCopyMode = False
Selection.Copy
Range("H2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
With Selection.Font
.Color = -16776961
.TintAndShade = 0
End With
Selection.NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"
Range("G3").Select
End Sub]
Regards,
Chad
Bookmarks