Help... I need some sleep!

[Brett Fields]

Every day I produce a spreadsheet (not of my own design) to report on the vaccination of our workforce. It needs to be out by 7:30 a.m. The raw data is available at 10pm the night before in 4 CSV files. I have 1 macro that imports the new info onto a sheet called "Data", and another macro that moves that data onto 2 other sheets ("EMS" and "Fire") in the required fashion.
The Problem:
Everything is great when I run this in the morning, but I'd like to sleep in on Sat & Sun. The way I have this written, it can't be run between 10pm & midnight the night before. I'd like to be able to do that for obvious reasons. Any ideas appreciated.

Public Sub AddRow()

Dim WSF As Worksheet
Dim WSE As Worksheet
Dim WSD As Worksheet
Set WSE = Worksheets("EMS")
Set WSF = Worksheets("Fire")
Set WSD = Worksheets("Data")

Dim FireLastRow As Long
Dim EMSLastRow As Long

Dim TblFire As ListObject
Dim TblEMS As ListObject
Set TblFire = WSF.ListObjects("TblFire")
Set TblEMS = WSE.ListObjects("TblEMS")

WSF.Activate
With TblFire
With .DataBodyRange
FireLastRow = TblFire.DataBodyRange.Rows.Count
'MsgBox (FireLastRow) 'just for testing
If .Cells(FireLastRow, 1).Value = Date - 2 Then '<---------------- This is what keeps me awake 'til midnight
TblFire.ListRows.Add 'add a row to the end of the table
Else
MsgBox ("The row count on the Fire sheet is not right." & vbCrLf & "Go back and fix that first")
GoTo StartEMS
End If

NewFireLastRow = TblFire.DataBodyRange.Rows.Count 'FireLastRow is redefined after the .listrows.add
'MsgBox (NewFireLastRow) 'just for testing
FireNextToLast = NewFireLastRow - 1
'MsgBox (FireNextToLast) 'just for testing
.Cells(NewFireLastRow, 1).Value = .Cells(FireNextToLast, 1).Value + 1 'add the next date to Col A:
.Cells(NewFireLastRow, 2).Value = WSD.Range("Q3") 'pull in the eligable employee number to col B:
End With

End With


StartEMS:
WSE.Activate
With TblEMS
With .DataBodyRange
EMSLastRow = TblEMS.DataBodyRange.Rows.Count
If .Cells(EMSLastRow, 1).Value = Date - 2 Then '<---------------- and here
TblEMS.ListRows.Add 'add a row to the end of the table
Else
MsgBox ("The row count on the EMS sheet is not right." & vbCrLf & "Go back and fix that first")
Exit Sub
End If

NewEMSLastRow = TblEMS.DataBodyRange.Rows.Count 'EMSLastRow is redefined after the .listrows.add
EMSNextToLast = NewEMSLastRow - 1
.Cells(NewEMSLastRow, 1).Value = .Cells(EMSNextToLast, 1).Value + 1
.Cells(NewEMSLastRow, 2).Value = WSD.Range("Q2")
End With

End With



'MsgBox (TblFire.DataBodyRange.Cells(NewFireLastRow, 1).Value) 'just for testing

End Sub


BTW, I'm always interested in anything else that cleans up my code or adds "Cool" factor. Just sayin'

 

[steve the fish]

If you do want to run between 10 and midnight i presume you just want to delete 1 day from the date? So instead of Date - 2 you could try:

Date - 2 + (Time >= TimeSerial(22, 0, 0))

 

[Brett Fields]

If you do want to run between 10 and midnight i presume you just want to delete 1 day from the date? So instead of Date - 2 you could try:

Date - 2 + (Time >= TimeSerial(22, 0, 0))

I was thinking about a 2 plus something concept but didn't have any idea of what the something should be. I've never used TimeSerial before. I'll read up on it.

 

[Brett Fields]

If you do want to run between 10 and midnight i presume you just want to delete 1 day from the date? So instead of Date - 2 you could try:

Date - 2 + (Time >= TimeSerial(22, 0, 0))[/COD
[/QUOTE]

I was thinking about a 2 plus something concept but didn't have any idea of what the something should be. I've never used TimeSerial before. I'll read up on it.

Just looking at this, wouldn't it need to be Date - 1 +(...... And more than that, wouldn't I need both and have to test for the date first then pick one?

 

[steve the fish]

I dont know? You had Date - 2. You wait until midnight so Date - 2 is still valid. Mine provides a way to not have to wait until midnight. Run from 22:00 to 23:59 it provides Date - 1. Run any other time Date -2. Why dont you test?