VBA to add filter and filter for latest date

[pscofe]

Hi, I've used VBAcode form other message boards to try and get this to work however it's only adding the filter to column A when I need the filter on for the entire top row ( I also want to filter column E if it contains "Receive") . In column A is a list of dates but I only want to see the Latest Date (won't always be yesterday, could be 4 or 5 days ago) Can someone help me amend this? Thanks

Sub PSRpt()

Dim myDate As Date

myDate = Application.Max(Columns(1))

ActiveSheet.AutoFilterMode = False
Columns(1).AutoFilter Field:=1, Criteria1:="=" & Format(myDate, [A2].NumberFormat)
Worksheets("PS Report").Range("B:B,C:C,F:H,K:Q,T:U,AF:AG,AK:AN,AP:AQ").EntireColumn.Hidden = True
Selection.Copy
Range("AR1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Comments"
Range("AQ1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False



End Sub

 

[pscofe]

can anyone help on this? Thakns

 

[Joe4]

Your issue with it only filtering column A is with this part here:

Columns(1).AutoFilter Field:=1, Criteria1:="=" & Format(myDate, [A2].NumberFormat)

The part in red is telling it the range to filter, which you currently only have set to the first column. Change this to the entire range you want to filter.

Note that when you want to get VBA code like this, you can often use the Macro Recorder.
If you turn on the Macro Recorder and record yourself manually doing what you want the code to do, it will give you must of the code that you need.
You may need to clean it up a little, or make it more dynamic (the Macro Recorder is very literal), but it will give you the building blocks you need to get started.

 

[pscofe]

Thanks. I've tried at amending to "Range("A1:AQ").AutoFilter Field:=1, Criteria1:="=" & Format(myDate, [A2].NumberFormat)" but that hasn't worked.

 

[Joe4]

Please show us an example of your data and expected output.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[pscofe]

I have a list of headers, I want to apply a filter across the top row and in column A to have the filter on latest date (which isn't always previous business day), thanks

 

[Joe4]

OK, to fix those filters, try replacing this part of the beginning of your code:

Sub PSRpt()

Dim myDate As Date

myDate = Application.Max(Columns(1))

ActiveSheet.AutoFilterMode = False
Columns(1).AutoFilter Field:=1, Criteria1:="=" & Format(myDate, [A2].NumberFormat)

with this:

Sub PSRpt()

Dim myDate As Date
Dim rng As Range

myDate = Application.Max(Columns(1))

'Find current region of range starting with cell A1
Set rng = ActiveSheet.Range("A1").CurrentRegion

rng.AutoFilter Field:=1, Criteria1:="=" & Format(myDate, [A2].NumberFormat)
rng.AutoFilter Field:=5, Criteria1:="receive"

 

[pscofe]

yes! That's worked perfectly, thank you for your help and this has massively helped my VBA knowledge (or lack of!)

 

[Joe4]

You are welcome.
Glad I was able to help!