Sort and filter on same column in vba

[Trickyaz1991]

hey guys,

can someone explain why this is not working?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


ActiveSheet.Range("$A$2:$J$2000").AutoFilter Field:=4, Criteria1:=Array("Materials" _
, "Materials NQ", "Materials AP", "Req Raised", "Req Raised NQ", "Req Raised AP", "Strip & Assess" _
, "WIP", "WIP NQ", "WIP AP"), Operator:=xlFilterValues

ActiveSheet.Range("$A$2:$J$2000").AutoFilter Field:=6, Criteria1:= _
"<=" & Application.WorkDay(Date, 15), Operator:=xlAnd

ActiveSheet.Range("$A$2:$J$2000").AutoFilter Field:=5, Criteria1:="<>"

ActiveWorkbook.Worksheets("Machining").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Machining").AutoFilter.Sort.SortFields.Add2 Key:= _
Range("F2:F2000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
:=xlSortNormal
With ActiveWorkbook.Worksheets("Machining").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With


End Sub

I have also attached an image of what I am trying to achieve.

I am trying to write VBA code that updates the sheet automatically, in column F "Revised Due Date" I want to filter the sheet to show me data within a certain data and then sort it
by ascending but some error keeps popping up?

any help would be helpful.

regards,

Aaron.

 

[DanteAmor]

but some error keeps popping up?

What does the error message say?
If you press the debug button when the error message appears, which line of the macro is highlighted in yellow?

While trying the following, but clear your macro from the Selection event, because that causes the code to run every time you select a cell.
Better put the following code in a module and associate the macro with a button.

Sub Sort_Data()
  With Range("A2:J2000")
    .AutoFilter 4, Array("Materials", "Materials NQ", "Materials AP", "Req Raised", _
      "Req Raised NQ", "Req Raised AP", "Strip & Assess", "WIP", "WIP NQ", "WIP AP"), xlFilterValues
    .AutoFilter 5, "<>"
    .AutoFilter 6, "<=" & Application.WorkDay(Date, 15)
    .Sort [F2], xlDescending, Header:=xlYes
  End With
End Sub

 

[Trickyaz1991]

Hey Dante,

please find image attached, this is what happens when i try the code you attached.

 

[DanteAmor]

This is my test data:

varios 07sep2020 modificar datos y footer en word.xlsm
	A	B	C	D	E	F
1
2	Job	Customer	Desc	Wip	MS	Rev due date
3	159	e	a5	Wip	Y	28-sep
4	155	k	a1	xd	Y	21-sep
5	158	d	a4	Strip & assess	Y	26-sep
6	164	t	a10	xd	Y	23-sep
7	156	b	a2	Strip & assess		24-sep
8	156	l	a2	xd	Y	25-sep
9	164	j	a10	Wip	Y	22-sep
10	157	m	a3	xd	Y	27-sep
11	155	a	a1	Strip & assess	Y	20-sep
12	158	n	a4	xd	Y	29-sep
13	160	f	a6	Materials	Y	30-sep
14	159	o	a5	xd	Y	01-oct
15	161	g	a7	Wip	Y	02-oct
16	160	p	a6	xd	Y	03-oct
17	162	h	a8	Wip	Y	04-oct
18	161	q	a7	xd	Y	05-oct
19	163	i	a9	Wip	Y	06-oct
20	162	r	a8	xd	Y	07-oct
21	157	c	a3	Strip & assess	Y	08-oct
22	163	s	a9	xd	Y	09-oct
Machining

This is the result after running the macro:

varios 07sep2020 modificar datos y footer en word.xlsm
	A	B	C	D	E	F
1
2	Job	Customer	Desc	Wip	MS	Rev due date
3	159	e	a5	Wip	Y	28-sep
5	158	d	a4	Strip & assess	Y	26-sep
9	164	j	a10	Wip	Y	22-sep
11	155	a	a1	Strip & assess	Y	20-sep
23
Machining

You could test the macro in a new workbook, on a new sheet, with my data sample.

If you have problems, you could put an example of your book in the cloud.
You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.