Results 1 to 5 of 5

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

  1. #1
    Member
    Join Date
    Aug 2011
    Posts
    92
    Rep Power
    14

    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.
    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
    Last edited by Rajesh Kr Joshi; 09-04-2012 at 03:20 PM.

  2. #2
    Member
    Join Date
    Jul 2012
    Posts
    55
    Rep Power
    13
    Quote Originally Posted by Rajesh Kr Joshi View Post
    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.

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #4
    Member
    Join Date
    Aug 2011
    Posts
    92
    Rep Power
    14
    Last edited by DocAElstein; 07-11-2023 at 12:42 PM.

  5. #5
    Member
    Join Date
    Aug 2011
    Posts
    92
    Rep Power
    14
    Hi, This is not working

Similar Threads

  1. Replies: 4
    Last Post: 07-02-2013, 11:32 AM
  2. Sort Data Using Formula To Find Top X
    By mahmoud-lee in forum Excel Help
    Replies: 12
    Last Post: 06-02-2013, 10:13 PM
  3. Replies: 2
    Last Post: 05-14-2013, 01:02 AM
  4. Replies: 6
    Last Post: 05-10-2013, 01:13 AM
  5. Sort Data When a Header Is Clicked
    By Rasm in forum Excel Help
    Replies: 9
    Last Post: 08-01-2012, 06:46 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •