VBA Autosort table by date
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: VBA Autosort table by date

  1. #1
    New Member
    Join Date
    Apr 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Autosort table by date

     
    Hello!
    I am looking for a way or VBA code(Unsure of what would work better) that would automatically sort the data coming into a table by current date to oldest date. Below is how i have my table set up, so ideally the table would automatically sort by column B once the data is inputted
    Column a b c
    Unit Type Move In Date Rent Amount
    3x3sw-d 06/26/2016 3,010.00
    2x2sw-bu 09/01/2015 2,345.00
    2x2sw-f 10/30/2016 2,660.00
    2x2sw-g 06/05/2014 2,470.00
    2x2sw-c 06/01/2012 2,325.00

    Any suggestions greatly appreciated

    thanks!

  2. #2
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,106
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Autosort table by date

    How is the data being entered into the table?
    Alan Sidman
    Win 10--Office 2016

    Click below for a white paper on Data Base Design
    http://r937.com/relational.html



  3. #3
    New Member
    Join Date
    Apr 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Autosort table by date

    i am running a report and inputting that report into another tab within the workbook. the table is then pulling from that report.

    thank you

  4. #4
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,106
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Autosort table by date

    Place this in the Workbook Event

    Code:
    Option Explicit
    
    
    
    
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Range("A1").CurrentRegion.Sort Range("B1"), xlAscending, , , , , , xlYes
    End Sub
    Alan Sidman
    Win 10--Office 2016

    Click below for a white paper on Data Base Design
    http://r937.com/relational.html



  5. #5
    New Member
    Join Date
    Apr 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Autosort table by date

    Thank you, but when i run the code i get a macros window to select the project location for the macros

  6. #6
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,106
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Autosort table by date

    I don't understand your response. Did you put the code in the Workbook Event or just in a module?

    With the VBE open, click on ThisWorkbook and then insert the code. It is Workbook Event and not a normal VBA module.
    Alan Sidman
    Win 10--Office 2016

    Click below for a white paper on Data Base Design
    http://r937.com/relational.html



  7. #7
    New Member
    Join Date
    Apr 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Autosort table by date

    Ok got it, now i am getting the response "To do this all the merged cells need to be the same size"

  8. #8
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,106
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Autosort table by date

      
    Merged cells are a disaster when it comes to Excel and VBA. VBA does not work well with merged cells. Smart Excel developers avoid using merged cells. In my opinion, they cause more issues than they are worth. I avoid using merged cells especially when working with VBA.
    Alan Sidman
    Win 10--Office 2016

    Click below for a white paper on Data Base Design
    http://r937.com/relational.html



User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com