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

**Rajesh Kr Joshi** · 09-04-2012, 03:17 PM

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.
Auto Shorting.xlsm

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

**Ingolf** · 09-04-2012, 09:10 PM

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.
Auto Shorting.xlsm

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.

**Excel Fox** · 09-04-2012, 10:29 PM

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** · 09-05-2012, 10:30 AM

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