Automatically converting to numbers and adding date on Paste + deleting certain dated entries on opening

[mtlguy123]

Hello!

Well, do I have a doozy for you. I've spent the better part of the past 3 days searching forums and manipulating suggested code to answer my own questions, to no avail. I now turn to you great folks for some possible help.

I work in admin at a high school. Every morning, I take the previous school day's tardy list and paste it into a workbook. I then get a running tab on every student's tardy record in an easy-to-read way (yes, a database is available, but my reasons for doing so go beyond this post's questions).

There are 4 columns of data that is pasted:
B: Student ID
C: Name
D: Tardy (either no data or the number of periods the student was late the previous day)
E: Tardy after lunch (an especially "gruesome" consequence awaits that cursed student )

I then manually insert the previous school day's date in column A.

This is done in tab "Late (Term)" and automatically mirrored in tab "Late (15 days)". If a student accumulates more then 3 tardies in 15 days, then he or she is bestowed with an after-school detention, hence the need for a mirrored tab (entries are manipulated and deleted in this tab).

I'm trying to automate 3 processes:

1. Upon pasting in cell (row, 2), I would like the previous school day's date (cell L2 in tab "Late (15 days)") to be inserted in column A of each pasted row.

2. For this same pasted set, I need to convert the data to numbers since it's downloaded as text (quite annoying). I currently have a macro button I click to convert all required columns, but if it could be done automatically upon pasting (either CTRL-V or right-click paste), it would be one less step.

3. Finally, in tab "Late (15 days)", I use the Workday function to show me the date 15 school days ago (cell D2). I manually delete entries earlier than that date. I would love for those entries to be automatically deleted upon opening the workbook.

So that's it. I don't usually ask for help, especially if the answer is already out somewhere on the interwebs... But I'm at a loss. Any help or guidance will surely be appreciated. It'll definitely avoid you getting detention .

If it helps, here's the Excel file (without the Master tab. Also, all names have been automatically generated, so no sensitive information is being shared).
https://drive.google.com/open?id=1zijK-7XUgGDsZmahj099ih2dvICAltA0

Thank you great community!

 

[mtlguy123]

OK, so I answered query #3 myself. The code is ugly, but it works:

Private Sub Workbook_Open()

Sheets("Late (15 days)").Select

Dim n As Integer
n = 5

Do Until Cells(n, 1) >= Cells(2, 4)
    
   If Cells(n, 1) < Cells(2, 4) Then
      Rows(n).Delete Shift:=xlShiftUp
   Else
       MsgBox "Nope"
   End If

Loop

End Sub

I'm sure there's a cleaner way, but at least it functions properly (as long as dates are in ascending order).

 

[mtlguy123]

OK, so I answered query #3 myself. The code is ugly, but it works:

Private Sub Workbook_Open()

Sheets("Late (15 days)").Select

Dim n As Integer
n = 5

Do Until Cells(n, 1) >= Cells(2, 4)
             Rows(n).Delete Shift:=xlShiftUp
Loop

End Sub

So simple...