VBA - Identify dead stock

JetSetDrive

New Member
Joined
Jul 26, 2019
Messages
14
I need assistance writing a code for identifying items not being sold within the last three months.

The last sale dates are in column P,Q,R

This is the last sale date at each of our three locations

I need to identify which rows have not had a sale in the last 3 months and copy and paste all data from the row into a new tab called "Dead Stock"

The information has a header that also needs to be copied over

Thank you in advance
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,205
Office Version
  1. 365
Platform
  1. Windows
Assuming that your data on the active sheet starts in cell A1, try this in a copy of your workbook.

Code:
Sub DeadStock()
  Dim wsOld As Worksheet, wsNew As Worksheet
  Dim rCrit As Range
  Dim lr As Long
  
  Set wsOld = ActiveSheet
  Sheets.Add(After:=wsOld).Name = "Dead Stock"
  Set wsNew = Sheets("Dead Stock")
  With wsOld
    lr = .Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set rCrit = .Range("A" & lr + 3).Resize(2)
    rCrit.Cells(2).Formula = "=MAX(P2:R2)<EDATE(TODAY(),-3)"
    .Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rCrit, CopyToRange:=wsNew.Range("A1"), Unique:=False
    rCrit.ClearContents
  End With
  wsNew.UsedRange.Columns.AutoFit
End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,842
Messages
5,525,171
Members
409,629
Latest member
McGuilliam

This Week's Hot Topics

Top