Macro for Autofiltering certain dates

[Simon]

Code below:

Summary per month is in column A, the month and year are in columns B
and C respectively.
in row 1 (columns H and I) I have the =DATE function to say what month
it is. Using these variables I wish to autofilter the A:C column data
for the latest month so I can present the latest column A number. I
need the data criteria to check the latest A:C data has been imported
for that month.


Sub Macro3()
'
Dim Retail
Dim Retailtest

Set Retail = Workbooks.Open("N:\mis\EXCEL\EoM\DW-Reports
\Retailfigure.csv")
Set Retailtest = Workbooks.Open("N:\mis\EXCEL\EoM\DW-Reports
\RetailfigureTEST.xls")

Retail.Activate
Range("A1:C100").Copy
Retailtest.Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("H1:I1").Copy ' Is =YEAR and =MONTH
Range("J1:K1").Select ''As values
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Dim Distribution1 As String, DistributionPath1 As Range
Set DistributionPath1 = Retailtest.ActiveSheet.Range("J1")

Dim Distribution2 As String, DistributionPath2 As Range
Set DistributionPath2 = Retailtest.ActiveSheet.Range("K1")

Range("A1:C1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:=DistributionPath1,
Operator:=xlAnd
Selection.AutoFilter Field:=2, Criteria1:=DistributionPath2,
Operator:=xlAnd

Set myRng6 = Range("A3000").End(xlUp)

If myRng6 = 0 Then
MsgBox "Retail hasn't run"
Else: 'MsgBox "Has Run"
End If

 

[Dave Peterson]

First, I'd put the date in one column--and it would be a real date. If I only
needed the month/year, then I'd use the first of the month for each of the
entries.

If you can't do that, then I'd have my code insert a new column and create that
date.

=date(h2,i2,1)
(and drag down)

Then I could use application.max() to find the largest date in that column.
Filter this helper column by that value and I'd be able to see the most current
entries in my data.