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

mtlguy123

New Member
Joined
Mar 5, 2019
Messages
3
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 ;):LOL:)

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 :ROFLMAO:.

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!
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

mtlguy123

New Member
Joined
Mar 5, 2019
Messages
3
OK, so I answered query #3 myself. The code is ugly, but it works:

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

New Member
Joined
Mar 5, 2019
Messages
3
OK, so I answered query #3 myself. The code is ugly, but it works:

Code:
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...
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,826
Messages
5,855,866
Members
431,771
Latest member
CoryMelth

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