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

[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.
379



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

[Ingolf]

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.
379



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


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:



ActiveSheet.PivotTables("PivotTable1").RefreshTable



Hope it helps.

[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

[Rajesh Kr Joshi]

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/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg (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=UgzMCQUIQgrbec400jl4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwhVTFaD469mW9wO194AaABAg.9gJzxwFcnPU9gORqKw5t W_ (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwhVTFaD469mW9wO194AaABAg.9gJzxwFcnPU9gORqKw5t W_)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugyb8nmKKoXvcdM58gV4AaABAg (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=UgwvvXcl1oa79xS7BAV4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgxvIFArksPprylHXYZ4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgxvIFArksPprylHXYZ4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Rajesh Kr Joshi]

Hi, This is not working:(