Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a series of data in columns posted to a new worksheet with VBA and I need to have it automatically sorted.
    I have coded a Worksheet_Change Event in the new Worksheet with ;


    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim VRange As Range
    Set VRange = Range("A2")
    If Not Intersect(Target, VRange) Is Nothing Then _
    Run "SortCols"

    End Sub

    And in the main module, I have the following code:

    Sub SortCols()
    Dim VRange As Range
    Dim a As Long
    Dim b As Long
    Dim c As Long
    Dim d As Long

    Dim TotalRows As Long
    Dim WsSource As Worksheet
    Set WsSource = Worksheets("Kintana for Neg CU Adjmt")
    WsSource.Activate
    Application.ScreenUpdating = False
    TotalRows = Application.CountA(Range("A:A")) - 1

    Worksheets(5).Range("A2" & TotalRows).Sort _
    Key1:=Range("C2"), Order1:=xlDescending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    Application.ScreenUpdating = True
    End Sub

    Whenever the columns are filled with data the Worsheet _Change event does not trigger the Run SortCols

    Any Ideas?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Your code runs SortCols only when the user modifies cell A2, because of the lines

    Set VRange = Range("A2")
    If Not Intersect(Target, VRange) Is Nothing Then _
    Run "SortCols"

    Since columns A should be sorted on column C, you must run SortCols whenever a cell in column C is changed:

    Set VRange = Range("C:C")

  3. #3
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='780334' date='17-Jun-2009 17:55']Your code runs SortCols only when the user modifies cell A2, because of the lines

    Set VRange = Range("A2")
    If Not Intersect(Target, VRange) Is Nothing Then _
    Run "SortCols"

    Since columns A:D should be sorted on column C, you must run SortCols whenever a cell in column C is changed:

    Set VRange = Range("C:C")[/quote]


    Thanks Hans

Posting Permissions

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