Thread: Auto sorting?

Turn on the macro recorder, let it record you highlighting all the data to sort, then turn off the recorder.

Now open the VBEditor (Atl-F11) and open the MODULE that was created (left pane) that now has your macro in it.

Copy all the code between the Sub / End Sub.
Doubleclick on the SHEET module (left pane) where you want this to work all the time and the sheet module will open.

At the top of the module enter the following header:

Code:

Private Sub Worksheet_Change(ByVal Target As Range) 

paste your code here

End Sub

Now, anytime you make a change of any kind to your sheet, the data will autosort. If that doesn't work, post up your final code and we'll help you "tweak" it.

When you post code, be sure to GO ADVANCED, past the code into the forum, highlight it, then click on the # icon to wrap it on code tags so that it is readable.

Tools > Macros > Record a new macro

In Excel, pressing F1 will popup the built-in help menu.

This is what I have! The first part, at top, is for my pivot tables! Any ideas?

Thanks

Code:

Option Explicit
Private Sub Worksheet_Activate()
ActiveSheet.PivotTables("TOTAL").PivotCache.Refresh
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
 Sort1()
'
' Sort1 Macro
'
'
    Application.Goto Reference:="R1C1:R10000C2"
End Sub

I don't see your question....just an "any ideas" query. That's not as helpful as you would think.

I kind of have this working now, however I'm having a few hiccups. It seems that it will automate before I completely finish typing. The document has 2 columns of data that need to move together. Basically, column A has a name, and B is data that supports it. I need them to both move.

The automate works great except for 1 hitch......as soon as I type something into Column A and hit enter, it auto sorts. I need to type data into column B too and hit enter, before it decides to automate. Once that happens, it'll be PERFECT!!

Thanks again JBeaucaire for the help so far, it's appreciated!!

Just to save time, can you post the sheet you're working on so I can tweak it directly?

GO ADVANCED > paperclip icon.

Thanks.

You just need the worksheet code to evaluate BOTH cells before it actually moves anything, so I added like so:

Code:

Option Explicit

Private Sub Worksheet_Activate()
    ActiveSheet.PivotTables("HI2").PivotCache.Refresh
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:B")) Is Nothing Then _
    If Cells(Target.Row, "A") <> "" And Cells(Target.Row, "B") <> "" Then _
    Columns("A:B").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End Sub

OK, try this:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:B")) Is Nothing Then _
    If (Cells(Target.Row, "A") <> "" And Cells(Target.Row, "B") <> "") Or _
        (Cells(Target.Row, "A") = "" And Cells(Target.Row, "B") = "") Then _
        Columns("A:B").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End Sub