Update Data in worksheet on Workbook Open

How to update data in worksheet on workbook open.

A user inputted data like this in January.
EXP DATE NAME
Jan. 27 ABC Trucking
Feb. 22 123 Logistics
May. 1 Red dog moving
June.9 Barts trucking

He opens it up on 4-24-18 it would look like this having updated the data in the following manner:
EXP DATE NAME
May. 1 Red dog moving
June.9 Barts trucking
Jan. 27 ABC Trucking
Feb. 22 123 Logistics

Watch the video below:

 

Watch this video on Youtube.

Here’s the complete VBA code to update the worksheet data automatically when the workbook is opened:

Dim count As Long

Sub moveOlderData()
Dim i As Long, lastrow As Long
lastrow = Sheets(“Sheet1”).Range(“A” & Rows.count).End(xlUp).Row

count = 0
For i = 2 To lastrow

If Cells(i, 1).Value < Date Then
count = count + 1
lastrow = lastrow + 1
Range(Cells(i, 1), Cells(i, 2)).Copy Range(Cells(lastrow, 1), Cells(lastrow, 2))
End If

Next i

End Sub

Sub deldata()
lastrow = Sheets(“Sheet1”).Range(“A” & Rows.count).End(xlUp).Row
lastrow = lastrow – count

For p = 2 To lastrow

If Cells(p, 1) < Date Then
Range(Cells(p, 1), Cells(p, 2)).ClearContents
End If

Next p

End Sub

Sub delBlanks()
Dim q As Long, lastrow As Long
lastrow = Sheets(“Sheet1”).Range(“A” & Rows.count).End(xlUp).Row
‘MsgBox lastrow
‘lastrow = Sheet1.Cells.Find(What:=”*”, After:=Range(“A1”), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For q = lastrow To 2 Step -1
If Cells(q, 1) = “” Then
Cells(q, 1).EntireRow.Delete
End If
Next q

End Sub
Private Sub Workbook_Open()
moveOlderData
deldata
delBlanks
End Sub

 

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

Leave a Reply

Your email address will not be published. Required fields are marked *