Excel Macro to Sort Data if a value changes in defined range

Hi,

I am trying to auto short(descending) values if a value changes in range D4 to D9. Attaches is the excel file and below is the code I am trying (seems I am doing something wrong).

Please help.
Attachment 379

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D4:D8")) Is Nothing Then

Call DataS
End If

End Sub

Sub DataS()
 
Range("B3:E8").Sort _
 Key1:=Range("E3"), Order1:=xlDescending, Header:=xlYes
 
End Sub

---

Thanks
Rajesh

Quote:

---

Originally Posted by Rajesh Kr Joshi

Hi,

I am trying to auto short(descending) values if a value changes in range D4 to D9. Attaches is the excel file and below is the code I am trying (seems I am doing something wrong).

Please help.
Attachment 379

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D4:D8")) Is Nothing Then

Call DataS
End If

End Sub

Sub DataS()
 
Range("B3:E8").Sort _
 Key1:=Range("E3"), Order1:=xlDescending, Header:=xlYes
 
End Sub

---

Thanks
Rajesh

---

Put this in modul

Code:

---

Sub DataS()
 
Range("B3:E8").Sort _
 Key1:=Range("E3"), Order1:=xlDescending, Header:=xlYes
 
End Sub

---

Then you have to refresh pivot table after you insert or change data in sheet Data. Try something like this in sheet module:

Code:

---


ActiveSheet.PivotTables("PivotTable1").RefreshTable

---

Hope it helps.

Instead of doing a sort using VBA, why don't you sort the Pivot table totals column once? So each time you change a month, the pivot table will automatically be sorted without any VBA

Quote:

---

Originally Posted by Excel Fox

Instead of doing a sort using VBA, why don't you sort the Pivot table totals column once? So each time you change a month, the pivot table will automatically be sorted without any VBA

---

The summary table is connected with various graphs and changes the value once the pivot changes coz the sumary table is connected using vlookup. I cannt not create the desired graph using pivot table :(



https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwhVTFaD469mW9wO194AaABAg. 9gJzxwFcnPU9gORqKw5tW_
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugyb8nmKKoXvcdM58gV4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwvvXcl1oa79xS7BAV4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgxvIFArksPprylHXYZ4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

Hi, This is not working:(