Code for button to show dates which expire in 6 months and also by year end.

batman1 · Jun 29th 2016

VBA beginner

I am trying to create code for a button to show which items from an excel sheet will expire within 6 months from today and also a button to show which expire before year end.

I have a sample sheet which I am using to work on and learn the basic codes. The expiry dates are in column E

forum.ozgrid.com/index.php?attachment/69553/

Any help appreciated

M40wen · Jun 29th 2016

Re: Code for button to show dates which expire in 6 months and also by year end.

Assign this to your button

Code

Sub trythis()
Dim MyDte As Long, LstRw As Integer
If Sheets("Sheet1").AutoFilterMode Then
Sheets("Sheet1").AutoFilterMode = False
End If
On Error Resume Next
Sheets("Sheet1").ShowAllData
On Error GoTo 0
LstRw = ThisWorkbook.Sheets("Sheet1").UsedRange.Rows.Count


MyDte = DateSerial(Year(Date), Month(Date) + 6, Day(Date))
ActiveSheet.Range("$A$8:$F$" & LstRw).AutoFilter Field:=5, Criteria1:="<=" & MyDte


End Sub

Display More

batman1 · Jul 5th 2016

Re: Code for button to show dates which expire in 6 months and also by year end.

Many thanks for that.

Is it possible to change the cell colours to red of the dates expiring rather than remove the remainder of the table?

Code for button to show dates which expire in 6 months and also by year end.

Participate now!

Share

Users Viewing This Thread