Help... I need some sleep!

Brett Fields

New Member
Joined
Apr 12, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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'
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,423
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
Date - 2 + (Time >= TimeSerial(22, 0, 0))
 
Solution

Brett Fields

New Member
Joined
Apr 12, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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:

VBA Code:
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

New Member
Joined
Apr 12, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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:

VBA Code:
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

Well-known Member
Joined
Oct 20, 2009
Messages
8,423
Office Version
  1. 365
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,133,271
Messages
5,657,765
Members
418,412
Latest member
fehr56

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top