Sort and filter on same column in vba

Trickyaz1991

New Member
Joined
Mar 23, 2020
Messages
22
Office Version
  1. 365
Platform
  1. Windows
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.
 

Attachments

  • Capture.PNG
    Capture.PNG
    110.3 KB · Views: 8

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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.

VBA Code:
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

New Member
Joined
Mar 23, 2020
Messages
22
Office Version
  1. 365
Platform
  1. Windows
Hey Dante,

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

Attachments

  • Capture.PNG
    Capture.PNG
    7.2 KB · Views: 6

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
This is my test data:
varios 07sep2020 modificar datos y footer en word.xlsm
ABCDEF
1
2JobCustomerDescWipMSRev due date
3159ea5WipY28-sep
4155ka1xdY21-sep
5158da4Strip & assessY26-sep
6164ta10xdY23-sep
7156ba2Strip & assess24-sep
8156la2xdY25-sep
9164ja10WipY22-sep
10157ma3xdY27-sep
11155aa1Strip & assessY20-sep
12158na4xdY29-sep
13160fa6MaterialsY30-sep
14159oa5xdY01-oct
15161ga7WipY02-oct
16160pa6xdY03-oct
17162ha8WipY04-oct
18161qa7xdY05-oct
19163ia9WipY06-oct
20162ra8xdY07-oct
21157ca3Strip & assessY08-oct
22163sa9xdY09-oct
Machining


This is the result after running the macro:
varios 07sep2020 modificar datos y footer en word.xlsm
ABCDEF
1
2JobCustomerDescWipMSRev due date
3159ea5WipY28-sep
5158da4Strip & assessY26-sep
9164ja10WipY22-sep
11155aa1Strip & assessY20-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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,098
Messages
5,545,952
Members
410,713
Latest member
TaremyLunsil
Top