How to replace a criteria in AutoFilter by a variable?

[Pana1]

Hi!

I have this code:


Sub Macro4()
'
' Macro4 Macro
'

'
Range("A1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$E$25").AutoFilter Field:=1, Criteria1:="16/08/2008", _
Operator:=xlAnd
End Sub


Instead of having to insert "16/08/2008" as Criteria1, I would like to have a variable in there corresponding to the maximum value of a column containing dates.

Could anyone help? That would be extremely helpful and very much appreciated...

In this case the maximum would correspond to the most recent date in the DATE column....

DATE SELLER ITEM QUANTITY PRICE
01.01.2008 John SHOES 10 100
02.01.2008 John TROWSERS 20 110
03.01.2008 John SHIRTS 10 80
04.01.2008 John JEANS 25 90
05.01.2008 John SOCKS 20 95
06.01.2008 John SHOES 32 75
07.01.2008 John TROWSERS 45 65
08.01.2008 John SHIRTS 11 95
09.01.2008 John JEANS 12 12
10.01.2008 John SOCKS 14 13
11.01.2008 John SOCKS 10 100
12.01.2008 John SHOES 20 110
12.01.2008 John TROWSERS 10 80
12.01.2008 John SHIRTS 25 90
13.01.2008 John JEANS 20 95
14.01.2008 John SOCKS 32 75
15.01.2008 John SOCKS 45 65
16.01.2008 John SHOES 11 95
16.01.2008 John TROWSERS 12 12
16.01.2008 John SHIRTS 14 13

Thank you again...

 

[bobsan42]

Try to replace with MAX(A:A)

 

[Pana1]

Hi!

I tried replacing MAX(A:A) as the Criteria!:= but it doesn't work... It filters all the data without leaving anything out.

Do you have any further suggestion?

I would be very apppreciative of your help...!

 

[bobsan42]

since you are coding it assign the max(a:a) to a var
and then use the var as criteria: (i think it works ok)

Sub Macro4()
Dim dvarrr As Date
dvarrr = Evaluate("max($A$1:$A$100)")
Range("A1").Select
ActiveSheet.Range("$A$1:$A$100").AutoFilter Field:=1, Criteria1:=dvarrr
End Sub

*just adjust your ranges accordingly

 

[Pana1]

Hi

Thank you very mcuh for your help. It worked but I do have another problem.

I would like to place the macro in another worksheet called TRANSACTIONS. I therefore write in the macro before the code you sent me the following:

Worksheets("Transactions"). Activate

However at that point it gives me the error message:

Autofilter Method of Range classe failed

or also

Can't execute code in break mode.

The line it refers to is

ActiveSheet.Range("$A$1:$A$100").AutoFilter Field:=1, Criteria1:=dvarrr


Can you help me?

Thank you so much...

 

[bobsan42]

maybe autofilter is not enabled?

Worksheets("Transactions"). Activate
Range("A1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$A$100").AutoFilter Field:=1, Criteria1:=dvarrr

 

[Pana1]

Maybe you can help me better if I explain myself better:

I have two sheets, sheet1 (REGISTER) and sheet2 (TRANSACTIONS).

In sheet 1 I register every sale I make, one by one.

I already have a macro that copies and pastes all the sales in Sheet 1 to sheet2, so that in sheet 2 I have a general registry of all my sales.

I need, for invoicing, to create a macro that gets all the sales done in one day together. It must extract them from Sheet 2 and save them in a new workbook.

To do so, I need the macro to identify the most recent date in Sheet 2. This cell might be repeated several times, as I might sell more than once in one day.

After that, the macro should highlight the corresponding rows and copy-paste them in a new worksheet.

Would it be possible for you to find something that works?

This is where I have got so far:

Sub Dailysummary()
Dim pathname As String
Dim strDataNameDailySummary As String
Dim datnamedailysummary As String
pathname = Worksheets("Register").Cells(29, 5).Value
strDataNameDailySummary = Worksheets("Register").Cells(48, 5).Value
datnamedailysummary = pathname & "\" & strDataNameDailySummary
Worksheets("Transactions").Activate
Dim dvarrr As Date
dvarrr = Evaluate("max($A$1:$A$100)")
Range("A1").Select
ActiveSheet.Range("$A$1:$A$100").AutoFilter Field:=1, Criteria1:=dvarrr
Sheets("Transactions").Select
Sheets("Transactions").Copy
Cells.Select
Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        ActiveWorkbook.SaveAs Filename:=datnameclient1, FileFormat:=xlNormal, _
            Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
            CreateBackup:=False
        ActiveWindow.Close
        Sheets("Register").Select
        
End Sub

Thank you so much for your help, you are helping me big big time.

 

[bobsan42]

replace

Paste:=xlPasteValues
with
Paste:=xlValues
</pre>

 

[Pana1]

Thank you so much, we are almost there... I have 2 problems left:

1. For some reason when the filter gets activated it ignores the value that it should show and returns only blank cells. There might be something wrong with the setup of the varrr variable.

2. When it copies to a new workbook, it keeps the filter. I need it to "Paste Special" => "Values", so that it only get the values that have previsouly been filtered.

Please don't hate me.

Thank you again

 

[Pana1]

In fact, I think the variable is not working at all... If there are sales for two different days in the Sheet 2 (TRANSACTIONS), then it filters out all dates and returns only blank cells... It's as if it did the opposite of what it should do.