Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Automatically Sorting as You Enter Information.

Automatically Sorting as You Enter Information

by Allen Wyatt
(last updated October 5, 2018)

112

Pat wonders if there is a way to automatically sort every time she adds new data to a worksheet. Pat thinks it would be great, for instance, that when she adds a new name to a list of names that the names are automatically sorted to always be in order.

The only way that this can be done is by using a macro that is triggered whenever something new is entered in the worksheet. You can, for instance, add a macro to the code for a worksheet that is triggered when something in the worksheet changes. (You can view the code window by right-clicking the worksheet tab and choosing View Code from the resulting Context menu.) The following is an example of one such simple macro:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    Range("A1").Sort Key1:=Range("A2"), _
      Order1:=xlAscending, Header:=xlYes, _
      OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
End Sub

The macro assumes that you want to sort on the data in column A and that there is a header in cell A1. If the names are in a different column, just change the cell A2 reference to a different column, such as B2, C2, etc.

Of course, sorting anytime that any change is made can be bothersome. You might want to limit when the sorting is done so that it only occurs when changes are made to a specific portion of your data. The following version of the macro sorts the data only when a change is made in column A.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        Range("A1").Sort Key1:=Range("A2"), _
          Order1:=xlAscending, Header:=xlYes, _
          OrderCustom:=1, MatchCase:=False, _
          Orientation:=xlTopToBottom
    End If
End Sub

There are some drawbacks to using a macro to automatically sort your data. First, since you are using a macro to sort, the operation is essentially "final." In other words, after the sorting you can't use Ctrl+Z to undo the operation.

A second drawback is that data entry might become a bit disconcerting. For instance, if you use any of the above macros and you start to put names into the worksheet, they will be sorted as soon as you finish what is in column A. If your data uses five columns and you start your entry in row 15, as soon as you get done entering the name into column A (and before you enter data into columns B through E), your data is sorted into the proper order. This means that you will need to find where it was moved in the sort, select the proper cell in column B, and then enter the rest of the data for the record. Of course, the way around this is to add your data in an unnatural order—simply make sure that the name in column A is the very last thing you enter for the record.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9005) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Automatically Sorting as You Enter Information.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Selecting a Group of Words

Want to select a chunk of text in a document? Perhaps the easiest way to do this involves using the mouse in conjunction ...

Discover More

Removing All Macros

Macros are stored as part of a workbook so that they are always available when you have the workbook open. If you want to ...

Discover More

Relative References to Cells in Other Workbooks

When you construct a formula and click on a cell in a different workbook, an absolute reference to that cell is placed in ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (menu)

Sorting Letters and Numbers

Sorting information in a worksheet can be confusing when Excel applies sorting rules of which you are unaware. This is ...

Discover More

Controlling Sorting Order

When you sort information in a worksheet, you have control over the order in which that information is sorted. Here's a ...

Discover More

Sorting by Colors

Need to sort your data based on the color of the cell? Excel doesn't include this capability, but you can still do the ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is one more than 8?

2019-09-11 06:07:27

Willy Vanhaelen

@Keith
Are columns B and F empty?


2019-09-07 12:30:55

Keith

Trying to automtically sort 3 columns that start in C and go to E.

First Column is Store then Salesman then 3 month Total. I want to sort on Total (column E) Descending


2019-08-23 15:58:15

John H

@Willy

That worked absolutely perfectly, thank you very much!


2019-08-23 12:04:53

Willy Vanhaelen

@John
This macro should do the job:

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column > 2 Or .Columns.Count > 1 Then Exit Sub
Dim tmp As Variant
tmp = Cells(.Row, 3).Formula 'save contents
On Error GoTo Enable_Events
Application.EnableEvents = False
Cells(.Row, 3) = "#$"
If Application.CountA(Range(Cells(.Row, 1), Cells(.Row, 2))) = 2 Then
[A1].Sort Header:=xlYes, _
Key1:=[A1], Order1:=xlAscending, _
Key2:=[B1], Order2:=xlAscending
End If
Cells(Application.Match("#$", [C:C], 0), Selection.Column).Select
Cells(Selection.Row, 3) = tmp 'restore contents
End With
Enable_Events:
Application.EnableEvents = True
End Sub

The macro has the advantage that the cellpointer follows the record to it's new position after the sort so you can immediatly continue to enter data in the other columns. The macro will only sort if both date and time are entered. The order doesn't matter. Change Header:=xlYes to xlNo if you have no header. Note that [A1] is a shortcut for Range("A1")

Remember: this macro must reside in the worksheet's code page (not in a module). Right click the worksheet tab and select View Code. That's the place to be.

Let me know if it works to your liking.


2019-08-22 12:46:04

John H

Hello, I need help.

I'd like to automatically sort a TV Broadcast schedule. I'd like it if adding

A B C D E
Date Time League Channel Match-Up

I want to enter an event and have it sort by Date first and then Time.

Any thoughts?


2019-07-26 06:29:47

Willy Vanhaelen

@Lewis
This macro schould do the job:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Or Target.Columns.Count > 1 Then Exit Sub
Dim tmp As Variant
tmp = Cells(Target.Row, 2).Formula 'save contents
On Error GoTo Enable_Events
Application.EnableEvents = False
Cells(Target.Row, 2) = Chr(160)
Range("A1").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
Cells(Application.Match(Chr(160), Columns(2), 0), 1).Select
Cells(Selection.Row, 2) = tmp 'restore contents
Enable_Events:
Application.EnableEvents = True
End Sub

The macro has the advantage that the cellpointer follows the record to it's new position after the sort.
Change Header:=xlYes to xlNo if you have no header.

Remember: this macro must reside in the worksheet's code page (not in a module). Right click the worksheet tab and select View Code. That's the place to be.

Let me know if something is not working as you expect.


2019-07-25 13:36:12

Lewis

@Willy, that would be perfect. Thanks very much :)

The names are in column A and I would be very grateful if you could make the macro so that the cellpointer follows the record.
I've only just started looking into implementing macros so I don't have a very good understanding of the code as of yet


2019-07-25 09:41:57

Willy Vanhaelen

@Lewis
Well that's exactly what these macros do.

If you let me know in what column you enter the names to be sorted I can provide the right macro code for that column. If you like I can also make the macro so that the cellpointer follows the record to it's new position after the sort so you can immediatly continue to enter data in the other columns.


2019-07-24 06:26:23

Lewis

Hi is there any way to alter this macro so it sorts a column alphabetically? If not can you provide a macro that would do this? I have a list of names that will be added to over time and would like it to sort itself each time a new name is added.


2019-07-08 04:09:32

Sagvan

I have the same problem, and I cannot solve it. I need some help with these codes which I actually don't know anything about. Could you please send me a file with the code and data.


2019-07-07 05:35:44

Willy Vanhaelen

@Ashok Kumar
Excel sorts empty cells always last whatever sorting direction is chosen but for Excell a zero is a number and is sorted accordingly.


2019-07-06 17:20:04

Ashok Kumar

HOW TO OMIT NUMBER 0 WHILE SORTING THE COLUMN USING VBA?? I have been using VLOOKUP data in sorting column and it needs to omit the 0 while sorting it??


2019-06-22 12:19:21

Colin Wharton

@Willy

brilliant - thank you very much


2019-06-17 10:39:58

Willy Vanhaelen

@Colin
This macro should do it:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 3 Or Target.Column > 29 Then Exit Sub
Dim tmp As Variant
tmp = Cells(Target.Row, 1).Formula 'save contents
On Error GoTo Enable_Events
Application.EnableEvents = False
Cells(Target.Row, 1) = "#"
Range("A1").Sort _
Key1:=Range("AD1"), _
Order1:=xlDescending, _
Header:=xlYes
Cells(Application.Match("#", Columns(1), 0), Target.Column).Select
Cells(Selection.Row, 1) = tmp 'restore contents
Enable_Events:
Application.EnableEvents = True
End Sub

Keep me informed.


2019-06-16 11:25:49

Colin

@willy

Yes I want the total column to auto sort largest to smallest from top to bottom when I enter data onto all of the week columns.

Is this possible?


2019-06-15 11:48:26

Willy Vanhaelen

@Colin
What exactly is not working?
Perhaps you did copy the macro in a module. If so then it will not work: read carefully the last paragraph of my comment.

The macro I propose sorts only if you enter something in the column AC (Wk27). Perhaps you expect that it sorts if you enter something in any of the 27 week columns?


2019-06-12 08:06:03

Colin Wharton

@Willy

inserted code as advised and it doesn't work?

maybe me as I am an excel beginner when it comes to this level of detail

colin.wharton23@gmail.com


2019-06-11 11:51:25

Willy Vanhaelen

@Colin
Assuming you want the sort to happen when you enter the last value in column 29 or AC (WK 27), this macro should do the job:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column<>29Or Target.Cells.Count>1 Then Exit Sub
Dim tmp As Variant
tmp = Cells(Target.Row, 1).Formula 'save contents
On Error GoTo Enable_Events
Application.EnableEvents = False
Cells(Target.Row, 1) = "#"
Range("A1").Sort _
Key1:=Range("AD1"), _
Order1:=xlDescending, _
Header:=xlYes
Cells(Application.Match("#", Columns(1), 0), "AC").Select
Cells(Selection.Row, 1) = tmp 'restore contents
Enable_Events:
Application.EnableEvents = True
End Sub

The macro has the advantage that the cellpointer follows the record to it's new position after the sort so you can immediatly see the effect.

Remember: this macro must reside in the worksheet's code page. Right click the worksheet tab and select View Code. That's the place to be.

Let me know if it works to your liking.


2019-06-10 10:00:45

Colin

Hi I am an excel newbie and need help from someone if possible, basically I have a spreadsheet that's set across 27 weeks that calculates games won etc. I would like the total to calculate [done] but to also sort most wins to least - tried the formulas below but don't really understand why its not working.

The spreadsheet is set us as

Name Team Wk 1 Wk 2 Wk 3 Wk 4 Wk 5 Wk 6 Wk 7 Wk 8 Wk 9 Wk 10 Wk 11 Wk 12 Wk 13 Wk 14 Wk 15 Wk 16 Wk 17 Wk 18 Wk 19 Wk 20 Wk 21 Wk 22 Wk 23 Wk 24 Wk 25 Wk 26 Wk 27 QTY

columns A1 to AD1

AD1 being the column I have to auto sort most to least

help me please!!!!

Colin


2019-05-19 11:33:35

Willy Vanhaelen

@Theodore MacAulay
If you only specify a single cell to the Sort Method, Excel assumes you want to sort the CurentRegion. So in your case the upper left cell of the sort region will allways be cell A1 even if you change it to Range("A4").Sort . If you don't want this you have to specify the exact region to sort, for example Range("A4:D50") which is difficult in this case since the range will change if you add records.

A solution is to insert a blank row above the header on the 3rd row which will then move to row 4. So your other 2 header rows will be "isolated" from your data. You can hide this blank row if you like.


2019-05-17 12:07:17

Theodore MacAulay

if I want to sort everything from A4 onwards how would I change this code?
I tried changing the Range("A1").Sort Range to Range("A3").Sort and it still sorted my header in row 3 with the other information. (my file has 2 headers)
I restarted and changed it to Range("A4").Sort and it still sorted it the exact same way, starting all the information at A2.
Ideally I will have one header up top, one header in the 3rd row and then the table of information that gets sorted automatically.
Thank you for the help so far, it's very interesting


2019-04-20 13:29:59

Willy Vanhaelen

@Max
Try to add a second
    If Not Intersect( ...
...
End If
set of lines and adjust for your second rules.


2019-04-19 08:45:05

Alan Elston

Hello Max,
As you imagined, it is likely easy to do what you want, and with VBA there are probably a lot of ways to do it.
It is a bit difficult in the confines of a Blog Comment section to give you a solution.
You would be best asking at an Excel Help Forum.
Remember to reference this Blog site, and this page ,
( excel.tips.net/T009005_Automatically_Sorting_as_You_Enter_Information.html ) ,
as the source code which you would like help in modifying.
At an Excel help Forum it is easier to exchange coding, maintaining formatting etc. Usually you can upload a file as well. So best is to make a reduced size desensitized data example file to demo what you have ( Before ) and then hand filled in by you, possibly in a second worksheet or second File, you can show the outcome you want, ( After )

If you are not in a rush, then I can help you here:
excelfox.com/forum/forumdisplay.php/2-Excel-Help

If it is more urgent , then try one of the more popular Excel Help Forums. You will usually get quick help there, as long as you carefully and fully explain your requirement , including a Before and After type data example, as I described.

Alan Elston


2019-04-18 08:19:04

Max

Is it possible to combine two of this type of rule? I am currently using this rule to sort via "required date" in column G and it works perfectly. I would like to include a rule superceeds this rule if a cell in column J "Priority" is changed to "Urgent" and the urgent row is now moved tot he top, regardless of the "required date" value? I imagine this is possible but im not knowledgeable enough in vba.
Thanks.


2019-03-25 06:04:14

Andy

Hi @Willy,

Thanks for the reply, still hasn't worked I'm afraid. Not sure what I'm doing wrong but something is preventing it from working. Not to worry, I've come up with an alternative solution to simply highlight the cells of any dates which are in the past, thereby identifying where a review is overdue.


Thanks


2019-03-23 13:03:46

Willy Vanhaelen

@Andy
Try this macro:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 5 Or Target.Cells.Count > 1 Then Exit Sub
Dim tmp As Variant
tmp = Cells(Target.Row, 6).Formula 'save contents
On Error GoTo Enable_Events
Application.EnableEvents = False
Cells(Target.Row, 6) = "#"
Range("A1").Sort Key1:=Range("I1"), Order1:=xlAscending, Header:=xlYes
Cells(Application.Match("#", Columns(6), 0), 3).Select
Cells(Selection.Row, 6) = tmp 'restore contents
Enable_Events:
Application.EnableEvents = True
End Sub

The macro has the advantage that the cellpointer follows the record to it's new position after the sort so you can immediatly continue to enter data in the other columns.

Remember: this macro must reside in the worksheet's code page. Right click the worksheet tab and select View Code. That's the place to be.
I assumed columns A till D are part of the table to sort.

Let me know if this macro solves your problem.


2019-03-21 09:02:23

Andy

Hi,

I have tried the above and cannot get it to work. I'm not sure what I'm doing wrong. This is for a tracker so I am recording the date of last review in column E, with date of next review then populated in column I using a formula of just extending the date by six months.

I just want column I to auto-sort by date whenever the date in column E is updated, so the next ones that need reviewing are at the top of the worksheet.

Using Excel 2010 with date in format DD/MM/YYYY

Thanks in advance


2019-03-06 13:34:27

Willy Vanhaelen

@Matt
Try this one:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Intersect(Target, Union(Range("AW2:AW22"), _
Range("AY2:AZ22"))) Is Nothing Then Exit Sub
Range("AR2:AZ22").Sort Header:=xlYes, _
Key1:=Range("AZ3"), Order1:=xlAscending, _
Key2:=Range("AY3"), Order2:=xlAscending, _
Key3:=Range("AW3"), Order3:=xlAscending
End Sub


2019-03-05 13:00:44

Matt

I've changed it now, this is for a Football league table. Could you copy the code and paste it back to me with corrections as it currently doesn't work. Not sure why though.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("AR2:AZ22")) Is Nothing Then
Range("AR2:AZ22").Sort Header:=xlYes, _
Key1:=Range("AZ3:AZ22"), Order1:=xlAscending, _
Key2:=Range("AY3:AY22"), Order2:=xlAscending, _
Key3:=Range("AW3:AW22"), Order3:=xlAscending
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End Sub


2019-02-23 10:42:46

Willy Vanhaelen

@Matthew
Sorry, this is byond the scope of this blog.


2019-02-21 20:00:56

Matthew

Could i send you the excel document where i want the code to be on


2019-02-21 12:33:03

Willy Vanhaelen

@Matthew
You can use the macro recorder to record the code for a 4 coiumn sort but this code is fairly complicated.


2019-02-21 10:33:15

Matthew

@Willy Vanhaelen

Is there not a way to sort 4 columns then?
Could I send you the Excel Document which I want the code to be on?


2019-02-20 11:22:10

Willy Vanhaelen

@ Matthew
You probalby placed the macro in a module. This doesn't work. Since it is an event macro it must reside in the code page of the sheet you want to use it in.

Right click on the tab of that sheet and select View Code... That's the place to be.

The Sort Method in VBA only accepts 3 columns. To sort on 3 columns you must add 2 ranges to the Sort Method like:

Range("AA3").Sort Header:=xlYes, _
Key1:=Range("BB4"), Order1:=xlAscending, _
Key2:=Range("AA4"), Order2:=xlAscending, _
Key3:=Range("AB4"), Order3:=xlAscending


2019-02-19 18:53:53

Matthew

Why is this currently not working for me? What have a done wrong?
Also, how do you auto sort more than one column?
i want columns; BB, then AA, AB and AC

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("A:BB")) Is Nothing Then
Range("BB3").Sort Key1:=Range("BB4"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End Sub


2019-02-19 18:31:11

Matthew

Why is this currently not working for me? What have a done wrong?
Also, how do you auto sort more than one column?
i want columns; BB, then AA, AB and AC

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("A:BB")) Is Nothing Then
Range("BB3").Sort Key1:=Range("BB4"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End Sub


2019-02-03 10:58:29

Willy Vanhaelen

@Pranav
Oops, little typo: it is row 3 that must be empty since row 4 contains you headers.


2019-02-03 10:42:13

Willy Vanhaelen

@Pranav
I realized something similar but in your case columns A and G must be empty as well as row 4. This macro will do the job:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 3 Or Target.Cells.Count > 1 Then Exit Sub
Dim tmp As Variant
tmp = Cells(Target.Row, 2).Formula 'save contents
On Error GoTo Enable_Events
Application.EnableEvents = False
Cells(Target.Row, 2) = "#"
Range("C4").Sort Key1:=Range("C4"), Order1:=xlAscending, Header:=xlYes
Cells(Application.Match("#", Columns(2), 0), 3).Select
Cells(Selection.Row, 2) = tmp 'restore contents
Enable_Events:
Application.EnableEvents = True
End Sub

The macro has the advantage that the cellpointer follows the record to it's new position after the sort so you can immediatly continue to enter data in the other columns.

Remember: this macro must reside in the worksheet's code page. Right click the worksheet tab and select View Code. That's the place to be.

Let me know if something is not working as you expect.


2019-01-31 19:47:18

Pranav

Hello. I have 5 columns of data, with the header for the first column in B4. The data in column C are dates, and I would like to add this code such that all the rows shift around when I add data to this table according to date, where the earliest date is at the top of the table. Could someone help to edit the code such that this happens, as I tried with the code above and the ranges altered but wasn't able to see any results. Thank you very much!


2019-01-09 10:59:50

Kim

I used the second example above and had it working perfectly one day. Then when I went to use the spreadsheet again the next day, the auto sort ability no longer worked. Now I removed the code and then added it back and it does not work. Does anyone know why it would work perfectly one day and then never work again?


2019-01-08 03:28:02

Barry

@Neda

I have run macro on protected & shared workbooks though not both conditions together. Macros do work on workbook configured this BUT you have to work within the restriction that these configurations impose (otherwise you will get an error). For example if if cell A1 is protected from being changed if a macro tries to right to that cell it won't be allowed to and will cause an error. The standard way to work around this is to allow the macro to unprotect the workbook/worksheet make whatever changes are required then re-apply the protection. However, I'm not sure of the implication in a shared environment.

Excel really doesn't work well in a shared environment, with many reports of lost data. Each user gets a copy of the workbook, makes changes on this copy and then this eventually is 'merged' with the master copy held on the server. I don't know what would happen if this violates the protected data in the master copy, but I suspect an error would occur. I would try it, but as a rule I wouldn't protect objects that you want the macro to be able to change.


2019-01-07 06:32:55

Willy Vanhaelen

@Neda
Since I never work with shared workbooks I can't assist ypou on this.


2019-01-05 08:14:49

Neda

I find that. now I have another question. My excel is on the protect and share workbook, how can I run VBA code in this mode?


2019-01-05 04:55:36

Neda

Thanks a lot!!!
It's perfect. If I want to sort by some specific color and then by date, who can I do this?


2019-01-03 15:01:14

Willy Vanhaelen

@Neda
This macro will probably suit your needs.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 10 Or Target.Columns.Count > 1 Then Exit Sub
Dim tmp As Variant
tmp = Cells(Target.Row, 11).Formula 'save contents
On Error GoTo Enable_Events
Application.EnableEvents = False
Cells(Target.Row, 11) = "#$"
Range("J1").Sort Key1:=Range("J1"), Order1:=xlAscending, Header:=xlYes
Cells(Application.Match("#$", Columns(11), 0), 10).Select
Cells(Selection.Row, 11) = tmp 'restore contents
Enable_Events:
Application.EnableEvents = True
End Sub

The macro has the advantage that the cellpointer follows the record to it's new position after the sort so you can continue to enter data in the other columns.

Remember: this macro must reside in the worksheet's code page. Right click the worksheet tab and select View Code. That's the place to be.

Let me know if something is not working as you expect.


2018-12-31 03:13:30

Neda

the column 10, yes it has. I want that whole column sort automatically by this column.


2018-12-28 11:22:28

Willy Vanhaelen

@Neda
On what column do you want to sort when you add new information in it?
Has it a header?


2018-12-27 13:46:24

Neda

I have 14 columns and I sort them by special word in one column that have a special color. Do I want to sort Automatically when new information added into them?


2018-11-09 03:59:34

Barry

@Nate

If you make the data into an Excel "Table" and use the totals row facility built into the table architecture then sorting the 'table' will leave both the header and total (footer) in their correct places.


2018-11-08 12:09:29

Willy Vanhaelen

@Nate Coyle
That's a tough one. It's easy to exclude the header from the sorting because it stays put but the totals row moves each time you insert a new record.

A possible solution is to make sure that, in the column you sort on, the cell of the totals row is empty. Excel always sort an empty cell last.


2018-11-05 16:59:47

Nate Coyle

I used your first code to sort my spreadsheet and it worked phenomenally. So well in fact that it included my total row too. So it took the row that had my pct total in it and moved it up into the middle of the spreadsheet. Is there a way to do this so that my total row is not included in the selection?


2018-10-17 12:38:16

Willy Vanhaelen

@Mark
You'll have to add a second sort key (Key2;= ...) which will permit Excel to make the difference.


2018-10-16 06:20:53

Mark

I'm using the first example.....

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Range("A1").Sort Key1:=Range("A2"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub

......to automatically sort a rota, so than when person A works overtime, they go to the bottom of the list. This works perfectly, with the only exception being if person B works overtime, their overtime total (value in column a) then matches that of person A both now having worked 1 overtime. I want person B to go to the bottom of the list, so that person A gets the next opportunity. Excel sorts the column but where the values are the same, the newly sorted data goes above, rather than below. I'm not quite sure how to tweak this so that the sort is done by number, and ensuring a duplicate value is then sorted again to the bottom.

Any ideas?

Thanks in advance.


2018-05-12 11:24:04

Willy Vanhaelen

@Stephen
This is strange. In my Excel 2007 when I use xlDescending it exactly does that.

Try to remove:
   OrderCustom:=1,
because that can disturb the sorting if you don't need it.


2018-05-11 16:41:37

Stephen R

@Willy Vanhaelen

I have already tried that, and it simply continued to ascend the numbers. I've also tried changing the command TopToBottom to BottomToTop, so I don't know if I would have to change one of the "KeyOrders" or something, but I've tried those two things at the very least. Could it be because I'm working with a version of Excel that's from 2016 or later?

Also, here's the code after I've changed it (I'm trying to get it to automatically sort Column B. Where Column B would have the numbers & Column A would have the names next to it)

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Range("B1").Sort Key1:=Range("B2"), _
Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub


2018-05-11 16:03:50

Stephen R

@Willy Vanhaelen

I have already tried that, and it simply continued to ascend the numbers. I've also tried changing the command TopToBottom to BottomToTop, so I don't know if I would have to change one of the "KeyOrders" or something, but I've tried those two things at the very least. Could it be because I'm working with a version of Excel that's from 2016 or later?

Also, here's the code after I've changed it (I'm trying to get it to automatically sort Column B. Where Column B would have the numbers & Column A would have the names next to it)

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Range("B1").Sort Key1:=Range("B2"), _
Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub


2018-05-11 06:51:28

Willy Vanhaelen

@Stephen
Replace xlAscending by xlDescending


2018-05-10 22:24:52

Stephen R

So I have the same VBA code written down, however, I'm trying to have it sort automatically in a descending order (highest on top, lowest on the bottom) & I can't quite remember how. Could someone please give me a hand if they are able?


2018-04-11 12:22:07

Willy Vanhaelen

@ETL
Probably you have a typo somewhere. Because of the 'On Error Resume Next' line, errors are skipped without warning. To disable the On Error Resume Next, put an apostrophe in front of it: 'On Error Resume Next and enter something new in column A. Then probably the macro will indicate the line where an error occurs.


2018-04-09 17:09:38

ETL

Not sure what I'm doing wrong.. I copied the code into the worksheet. I entered data in column A, and my Undo button grayed out (indicating that a macro was run), but the data wasn't sorted! Really not sure how I could've messed this up.


2017-06-05 02:08:25

Vinesh

My data in sheet 4. I wants to sort values Automatically from A7 To F506 by Using A1 Column Values. I used Macro Given below :

Sub sb_VBA_Sort_Data()
Range("A7:F506").Sort _
Key1:=Range("A1"), Header:=xlYes
End Sub

It Works. But when we open file again it shows run time error 1004 Sort method of range class failed. Please help me.


2016-12-31 13:04:58

Willy Vanhaelen

@Davis

Which columns do you use and on what column do you want to sort?


2016-12-30 07:26:48

Davis

I would like to sort data in rows and 6 columns from smallest to largest automatically. What is the code for that?
I use custom sort but it eats my time because i have to do it all over again for the other rows, please assist.


2016-12-08 11:45:16

Excel-ant

Is there any way (short of VBA, on which I'm terribly weak) to set a workbook to automatically sort a worksheet every time that it's opened?

The column which I'll be sorting is dependent on a VLOOKUP referencing another cell, which will be populated with =TODAY(), so the values will change each day that the file is opened.

Any assistance you can provide would be greatly appreciated.


2016-11-22 01:21:26

werner

I am busy drawing up karate draw sheet, i want the names to be sorted into a draw sheet automatically when i add names. They must be sorted according to age, belts and gender: for example: all white belts male 6 years and younger together.


2016-11-07 20:00:56

Stephen

How can I sort based on multiple criteria (using excel 2013)? I have Cell A3 (Priority), Cell B3 (Squadron), and Cell C3 (Flight). The corresponding data starts on Rows 4-60. The List starts on Row 3, because I have some other informational rows on rows 1 and 2. I would like it to auto sort based on Column B (Squadron), then Column C (Flight), then Column A (Priority). I would like it to auto sort only when Column A is updated.


2016-11-01 04:23:03

L

I am using the macro detailed on this page to automatically sort a table of data, which works fine. However the macro only takes effect when I type something on the worksheet the table is located on. What I am trying to do is enter data on a separate worksheet. There are then various formulas picking this information up and putting it in several tables on different worksheets. I need the macro to activate and sort the table as soon as the data is entered on the other worksheet.


2016-10-05 14:06:17

Sandip

Sir,
pls. help me for following condition.
if column A contains 5 row data, and column B is their values. I want formula that if I press any data which is in column A, the value of that data in column B, should be automatically print in that cell.
Pls. help.


2016-09-24 12:31:01

Willy Vanhaelen

@Mike S

This macro should do the job:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Or Target.Columns.Count > 1 Then Exit Sub
Dim tmp As Variant
tmp = Cells(Target.Row, 1).Formula 'save contents
On Error GoTo Enable_Events
Application.EnableEvents = False
Cells(Target.Row, 1) = "#$"
Range("A1").Sort Header:=xlYes, _
Key1:=Range("C1"), Order1:=xlAscending, _
Key2:=Range("B1"), Order2:=xlAscending
Cells(Application.Match("#$", Columns(1), 0), 2).Select
Cells(Selection.Row, 1) = tmp 'restore contents
Enable_Events:
Application.EnableEvents = True
End Sub

The macro has the advantage that the cellpointer follows the record to it's new position after the sort.

Remember: this macro must reside in the worksheet's code page. Right click the worksheet tab and select View Code. That's the place to be.

Let me know if something is not working as you expect.


2016-09-24 09:27:10

Mike S

@ Willy Vanhaelen the list does not change it is an overtime sorting list with persons name and their seniority date that do not change then a date last worked that will be updated weekly. Essentially this one list is used to both select voluntantary and mandatory overtime so sorting by date last worked by itself can lead to an issue with someone being mandated ahead of someone with less seniority. Way more work than it needs to be in my opinion but if it is what I am told to do I am all about making it easier. I just have not played around with macros much so don't know where to start. Thanks for your response and help.


2016-09-23 13:15:00

Willy Vanhaelen

@Mike S.

It can be done but by adding records, it will be very unreliable.

When you enter data in A, B, and C consecutively this will happen:
1) Entry in A: Ok
2) Entry in B: will trigger the sort but C is still empty so the sort is wrong
3) entry in C: is recorded but you will have to re-enter the date to redo the sort

Not very elegant. If you have the discipline to always enter A, C, B it can work.

If it is a static list where you just want to re-sort when changing the date I can provide you a macro that can do it. Just let me know.


2016-09-22 23:46:46

Mike S.

Could some one help me design a macro to do the following: column A (names) column B (dates) column C (number) if date changed in B sort whole group by C smallest to largest then B earliest date to latest date automatically


2016-09-05 03:50:34

Sreerag

Im a newbie to excel so i don't know any technical details. Im using 2007 excel. I want to copy the whole column as i enter data to my main sheet(sheet 1) to the other sheets (named sheet 2,3,4 resp) and I want this to be copied according to the data i enter in Column 'S' of the main sheet. Please help. I have tons of data to be copied that I really get annoyed. So please.

U could also mail me the macro programs.
i would appreciate early replies.
Thank you.


2016-08-26 09:55:48

Willy Vanhaelen

@James

I don't think that a regular advanced sort as Barry proposes will solve your problem because all rows with an empty cell in column C will end up at the end of the table. What you discribe is in fact a conditional sorting and Excel doesn't provide that.

You can solve it with a help column though. Assuming column E is empty and your data starts at row 2, enter the following formula in E2:
=IF(C2<>"",C2,D2)
Copy this down as far as needed and sort on this column E.

I hope this solves your problem.


2016-08-25 16:16:02

Barry

@James

If you do not need to do this too frequently then using Excel in-built sorting functions will do this easily. In Excel 2003 select Data then Sort and then specify the columns you want sorted and whether the sort is ascending or descending, in Excel 2007 or later choose the Data tab, then "Advanced Sort....." and then select the column and sort order.

If you want to do this sort frequently then turn on the macro reorder,assign the macro to a shortcut key,
before doing the above and then turn it off once you've completed the task. Then simply use the shortcut to execute the Sort whenever you like.


2016-08-24 14:23:40

James

I am a total newbie in excel.

I have two columns I would like to sort:
1)Column C and D
2)Both columns are dates (eg. 15-Dec-16)
3)Column C sometimes will be blank

I would like Column C to sort first and if it is blank, to take into account the date in column D.

Please let me know if anyone can help.

Thank you!


2015-11-14 05:05:05

Barry

@Daniel

The tip http://excel.tips.net/T003116_Recording_a_Data_Entry_Time.html

should give yo uthe answer you want


2015-11-13 06:47:57

Daniel Sales

HI There,

I have a spread sheet where I am calling through contacts. i would like to apply a date in the date column and have it automatically update rather than go to "sort & filter" each time.

Can anybody please help?

Many thanks


2015-10-12 02:57:29

indra jit ghosh

coloumn A-roll
coloumn B- name
column C- rank
i want to do that column d posses rank wise name with automatically changing the valu of c.
how can i do that? please suggest some thing to get rid ths problem.


2015-10-06 06:10:07

NILESH

I HAVE RECORDED MACRO TO SORT DATA
IT WORKS FINE,
BUT WHEN I SAVE FILE
NEW FILE OPENS

HOW TO AVOID THIS


2015-10-05 20:21:50

Garetth

Hey

Please help, Im doing a local soccer tournment on my work and I have all the information on excel, but is complicated to update every week the results, goals, points, etc.

So could you help me with some macro to auto sort the positions?

Like

Column A = Gral Position (aka 1,2,3,4,5_
Column B = Team
Column C = wins

So auto sort column C (and B attached to C)

any ideas?

Thank you!!!


2015-09-29 10:49:47

Willy Vanhaelen

@maryanne,

This macro will probably suit your needs.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Or Target.Columns.Count > 1 Then Exit Sub
Dim tmp As Variant
tmp = Cells(Target.Row, 2).Formula 'save contents
On Error GoTo Enable_Events
Application.EnableEvents = False
Cells(Target.Row, 2) = "#$"
Range("A1").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
Cells(Application.Match("#$", Columns(2), 0), 1).Select
Cells(Selection.Row, 2) = tmp 'restore contents
Enable_Events:
Application.EnableEvents = True
End Sub

The macro has the advantage that the cellpointer follows the record to it's new position after the sort so you can continue to enter data in the other columns.


2015-09-28 05:42:23

Barry

@maryanne,

What you want can be done, as using the solutions described in this tip and the various comments made.

The critical thing is determining the trigger for the sort. This has to be done in such a way that is not confusing/disconcerting for the User. That is you do not want to, say, enter a value is the bottom of column A and immediately sort then, so that the User then has to find the partial entry to complete the record.

As I have previously suggested the trigger could be on making the entry into the last column K, though obviously if column K entry is not made then the sort will not occur.

A more elegant solution would be to use a Userform for the User to enter the data and when the data is transferred to the table the sort can then be triggered at that point. You can also apply more rigorous data validation rules as well e.g. checking for certain fields to have values. But this is really in the realm of a competent VBA programmer.


2015-09-27 09:43:01

maryanne

Hi,

I am new with macro.

I want to customise a function that will automatically sort data alphabetically including the new added data.

SO i have the names in Column A and I have data til row K. I want column A to K to sort automatically every time I enter new data.

I have been trying this formula but cant seem to have it work.


2015-09-18 06:03:26

Barry

@Pheb

This a classic case for using a pivotable.


2015-09-18 03:57:06

Pheb

Hi Barry,

My apology for incomplete data. This is what I’m working with, I have a huge data containing of brands, average sales and branches. I need to classify how many brands contains average sales like 1) 100,000 and up 2) 50,000 to 99,999 3)49,000 and below and I need to do it in 53 branches. For now, I use conditional formatting in excel (Highlight Cells Rule) for me to separate data per branch then I copy paste (value only) each branches in a sheet then do a manual sorting. The problem is i really needed to update this monthly. 

Column C - Brands
Column D - 100,000 and up

Column F - Brands
Column G - 50,000 TO 99,999

Column I - Brands
Column J - 49,000 & below

If possible i want to automatically sort 3 columns that contain numbers in descending order(d,g,j)together with their brands (c,f,i). Any help would be appreciated.


2015-09-17 20:43:28

Tre

Thank you for your help. The code works great after changing the sort order for the right columns.


2015-09-17 14:18:17

Barry

@MichaelB

Your requirement is a lot more complicated. The primary issue is the wookbook with the table to be sorted just has a bunch of formulas which do not in themselves change (even though the value from the linked workbook does), therefore there is no trigger/event to fire off the macro.

You could just fire off the macro when the workbook is opened, and/or every few seconds/minutes/hours if an update is needed whilst the workbook is open. You would use the "On Time" function to trigger the sorting macro, and to reset the On Time function for the next sort interval. The refresh time should be longer than the time taken to refresh the linked data.


2015-09-17 14:01:49

Barry

@Pheb

Please clarify what columns are within the range you want sorted, and which column you want to sort by (and whether to sort ascending or descending values).


2015-09-16 22:12:08

MichaelB

I tried taking the top code and modifying it without luck to autosort all at once multiple (8) columns A thru H - which are linked to another workbook that has daily data changes. Can you assist me. Thnx Barry.


2015-09-16 05:33:02

Pheb


Hi!

I really need help!I want to automatically sort 3 columns in a excel sheet.
Column 1 - 50 & below
Column 2 - 51 - 100
Column 3 - 100 & up



Please provide a solution, Thank you in advance for your immediate response!


2015-08-29 14:26:26

Barry

@Mike

I got this working without any hiccups, but needed a little bit of tweaking of the code that the macro recorder produced.

You can download the file I created using this link:

http://www.buzzworks.co.uk/TriggeringSortingonDifferentSheet.xlsm


2015-08-29 13:36:07

Barry

@Mike.

Can you paste the code into this thread?

Barry


2015-08-28 15:06:29

Mike

Barry,

I've read your comments to SNZ which should apply to my question as well. I ran the recorder and sorted my data and have the code for that process. However, I still can't seem to get it to work when I put that code into sheet 1.

Thanks!
Mike


2015-08-28 14:37:53

Mike

Thanks! This was very helpful! However, I can't figure out how to make a small change.

How can I use this code to sort based on a change to a cell in a different sheet?

To be more clear, I want to auto sort columns B through L on sheet 2, based on a change to "M4" in sheet 1.


2015-08-27 05:22:09

Barry

@Nithin Antony

You need to be more specific on what you want.

1. what range do you want to be sorted?
2. what column(s) do you want to trigger the sort?

You need to take note of the comments in the main part of the tips regarding it being disconcerting to the User.


2015-08-27 05:16:39

Barry

@TRE

The following code is a variant of what I suggested to Nicolette on 27th March 2015

Please note I have not tested this code.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("E:E")) Is Nothing Then
With Worksheets("Sheet1").Sort
.SortFields.Clear
.SortFields.Add Key:=Range("A:A"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
.SortFields.Add Key:=Range("D:D"), _
SortOn:=xlSortOnValues, _
Order:=xlDescending, _
DataOption:=xlSortNormal
.SetRange Range("A:E")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ElseIf Not Intersect(Target, Range("L:L")) Is Nothing Then
With Worksheets("Sheet1").Sort
.SortFields.Clear
.SortFields.Add Key:=Range("K:K"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
.SortFields.Add Key:=Range("H:H"), _
SortOn:=xlSortOnValues, _
Order:=xlDescending, _
DataOption:=xlSortNormal
.SetRange Range("G:L")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If
End Sub

NB: you will need to change the sort order to suit your requirements what you want on each of the lines starting "Order:=" and change the parameter to either xlAscending or xlDescending as required.


2015-08-26 06:54:40

Nithin Antony

I want to automatically sort 4 columns in a excel sheet.
Column o - A to z
Column D - A to Z
Column j - A to Z
and Column G - Newest to Oldest.

Kindly provide a solution, Thank you!


2015-08-25 13:58:37

Tre

I could use some help with the code as well.

Columns A-E need custom alphabetical auto-sorting by column A first, then by D, once column E data has changed (alleviates data sorting before you enter all five fields). BUT, I also have a second set of columns... Columns G-L hold another set of data and they need to be custom alphabetical auto-sorted by column K first, then by column H, once column L data has changed (again alleviating auto-sort before all data across the row is entered).

I can see from the examples how the sort code works in general, but I cannot for the life of me, write the code to make even one set of cells auto-sort correctly based on what I have stated here. I can sort them manually this way using the custom sort I enter. But it seems like the custom sort only holds one set of values at a time as I have to re-enter the sort values each time I want to sort one or the other sets of columns.

Is my Excel not working right, or am I doing something wrong? Is there a way to code a macro for each set of columns separately and use their own custom sort values as well?

By the way, thank you very much for your help. I could not seem to find this close of an answer anywhere else.


2015-06-22 00:55:31

Anuj Sharma

The data which comes daily in my mail in excel sheet. i wants this data in my main excel sheet. all compinies data come in my mail in one excel sheet daily. but i wants one compnies data in my main sheet in one page 2nd compnies data in second page with in my main sheet and third compony data in third page please help me its a urgent for me


2015-03-27 05:41:15

Barry

@Nicolette

The following code should do what you want. The macro should be on the codepage for the sheet containing the data. It hasn't been tested.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("A:A")) Is Nothing Then
With Worksheets("Expenses").Sort
.SortFields.Clear
.SortFields.Add Key:=Range("D:D"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
.SortFields.Add Key:=Range("C:C"), _
SortOn:=xlSortOnValues, _
Order:=xlDescending, _
DataOption:=xlSortNormal
.SortFields.Add Key:=Range("H:H"), _
SortOn:=xlSortOnValues, _
Order:=xlDescending, _
DataOption:=xlSortNormal
.SortFields.Add Key:=Range("F:F"), _
SortOn:=xlSortOnValues, _
Order:=xlDescending, _
DataOption:=xlSortNormal
.SetRange Range("A:K")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If
End Sub

Note: this macro will run even if the value in Column A is changed to the same value i.e. the value doesn't actually change.


2015-03-26 15:56:33

Nicolette

Hi Barry,

I am having some trouble making this work for my circumstances. I have a huge data table that I'll need sorted automatically by changing anything in column A. The headers are A1:K1, and the data I need sorted automatically, with anything change to column A, is range A2:K1120.

But I'm sorting based on 4 different criteria:
1. Column D titled "Risk Category" - sorted on values, order is a series of risk category titles
2. Column C titled "Credit Line" - sorted on values, largest to smallest
3. Column H titled "YTD Outages" - sorted on values, largest to smallest
4. Column F titled "Payscore" - sorted on values, largest to smallest

How do I manipulate the code you've provided us to accommodate advanced sorting such as this, that is triggered by any change to column A?


2015-03-06 06:56:34

Barry

@Colleen,

I am not clear what precisely you want TBH.

You can just select a single cell in your column of data and then just sort that column. Excel will expand the range to be sorted to the whole table (bounded by empty row and empty column) and make a guess if there is a header row or not.Your data is sorted in date order (assuming dates are in a recognised Excel date format, and so everything is in blocks of data for each month. This won't give you any totals though.

To get totals you could use a pivot table. Pivot tables don't automatically update themselves; this would have to be done manually or have a macro do this for you.


2015-03-06 06:46:06

Barry

@Adarsh

In the VBA code above you can specify the range to be sorted in the line after the "IF" statement. For example:

Range("A12:A18").Sort - however the range will not expand and in particular any related data in the adjacent columns will not be sorted with this range. Using Range("12:18").Sort will select all the rows.

As you are only sorting a selection of the data the chances are that it doesn't include the header, in which case the parameter Header:= xlYes needs to be xlNo.

You will have to have a Sort statement for each worksheet, and specify the range for each sheet. These sort routine could be all in one subroutine which when run would sort every sheet. Depending upon the amount of data this could take some time, so you consider only sorting data if there is a change on the particular sheet concerned.


2015-03-06 06:29:51

Barry

@SNZ

On Sheet2 use the macro recorder then sort the data as you would normally do, turn off the macro recorder afterwards.

In the Visual Basic Editor open the codepage for Sheet 1 and create a event subroutine for when this worksheet changes. Copy the code from the macro you created (this will be one of the code modules) in the Worksheet_Change subroutine on the codepage for Sheet1.

Every time there is a change on Sheet1 the subroutine will run and sort the data on Sheet2.


2015-03-05 14:11:08

Colleen

Would like to know how to sort any data by the dates entered to separate into months. Specifically, enter data into column E on a end of the month basis and would like to update on a monthly basis without keeping track of each total.


2014-12-30 12:43:58

Adarsh

HI I wonder if you guys could help in following EXCEL data sorting problem.

I am working in Research firm,In one of my recent project i need to sort the data in only in specific portions of column (for Eg A12 to A18) whenever its get updated. i dont want to sort above or below of specified portion of the column and also if I sort it should expand its selections.

I am working with more than 80 sheet and different portion in different worksheet need to be sorted whenever it get updated....
Please help me, I thought of recording macro for each sheet is that work ?


2014-12-16 12:20:56

SNZ

Hi, I have an excel workbook with two sheets. I enter data on sheet1. On sheet2 I calculate scores based on the data entered on sheet 1. I want to sort sheet2 whenever I enter data on sheet 1. How can I do that. I'm a total newbie at excel macros. Any help would be appreciated.


2014-10-28 05:20:57

Igor Torrealbas

I often receive unsorted data from a laboratory. My reference for sorting is a column (lets call it A1:A160). I want to sort the new values as for this column automatically )they also are in two different workbooks.

Currently I do it by creating a custom list (with the drawback that it has limited space), then sort by this custom list. But everytime that I have to perform this, my reference list will change, so I have to do the whole process once and again.

Would there be a way to let's say "automatically create a custom list, and sort the values in a specified range as for that custom list when they are entered (pasted)"?

Take into consideration that I have never wrote one line of code, but I really want to learn, and this is a case that to start I think ;)

Thanks in Advance


2014-09-10 10:01:26

mark

what if i want to sort by two columns
column A first then if data is the same
sort by column C


2014-07-04 02:53:48

alireza dodangeh

It was a good answer
Thank


ArD


2014-03-10 13:42:11

EvanJ

What if the data you are entering is on another worksheet? I've got a workbook where I enter data in Sheet1 (column G) and link that data to Sheet2 (column F). I would like to create a Macro that auto-sorts Sheet2 by column F whenever the data in Sheet1 is updated, but everything I've tried so far has failed. Any advice would be much appreciated.


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.