# automatically sort in excel



## verobio (Oct 20, 2011)

Hi, I am trying to automatically have sorted in excel 2 columns in sheet 2 with data linked with sheet 1 so that when you enter data in sheet 1, the data in sheet 2 is being automatically sorted.

My data on sheet 2 are in 2 columns: Project ID and Score. The data are in cells B2 to B101 and C2 to C101.

The data in sheet 2 are linked to data on sheet 1. The "project ID" column (B2 to B101) of sheet 2 is linked to cells D8 to CY8 and the "score columns (C2 to C101) of sheet 2 is linked to cells D79 to CY79.

The idea is that when score is entered in sheet 1, the project ID and score of sheet 2 gets automatically sorted so that we see at the top which project has the highest ranking.

Can someone help me out with the code to use? I am a beginner...

Thank you!!

ps: it is quite urgent


----------



## RSpecianjr (Jan 20, 2010)

Hey verobio,

There are multiple ways to auto-sort. 

Formulas:

If you want to display small amounts of data based on a larger database, then formulas is probably the way to go. You would use some sort of lookup or rank based formula to do this. It is completely dependent on situation though.

Autofilters:

The quickest, but still manual, way is to use an autofilter. Select your data and press CTRL+SHIFT+L.

VBA (Macros):

VBA is used in Microsoft office application and can be used between the applications or strictly the application you are using. A Macro is the easiest way to duplicate a series of steps you take over and over again. It also has the important ability to run after certain events, this ability is primarily what power-users use to Auto-sort.

Alright, so you know what to use, but don’t know how write code. No problem, Microsoft created a very user-friendly feature to record steps. Record a Macro. In Excel 2007 you can find this option in the lower left hand corner of the screen. It looks like a small window with a red dot in the upper left hand corner. If you click on that it will prompt you to name it. Name it whatever you would like (no spaces), then hit OK. It is now recording what you do within Excel. Go through your steps to sort your data, it is important to only do what you want to have repeated. When you are done, click the stop button. It replaced the Record Macro button.

Now, you have a macro that you can run whenever you want. Unfortunately, it isn’t automatic yet. There is one more step, adding the code we recorded to an event. Press ALT+F11. This will open up the VBA Editor. On the left hand side you will see VBAProject, under that you will see Microsoft Excel Objects and Modules. Let’s do a quick explanation of what those are. 

Microsoft Excel Objects will host your Worksheets and then the Workbook (ThisWorkbook) itself. All event macros will be housed inside either one of those worksheets or inside ThisWorkbook. 

Modules will host your code that is not dependant on an event. This is where we will find our recorded Macro (this is only going to be viewable after you have recorded a macro).

So, expand the Modules folder and double click on Module1. You should see code in there. Copy your code without the start (Sub Macro1()) or end (End Sub). This will be replaced by the event trigger. Now double click (under VBA Project) on the Worksheet that you want your sort to happen on.

This should open up a blank page. Above the blank page, you will see two drop-down menus. That read General and Declarations. Change the General Drop-down to Worksheet. Now, what read Declarations should read SelectionChange and you should have a Private Sub populate. If you paste your code in there, whenever you go from one cell to another, it will run your recorded code. In that second drop-down, it will give you a bunch of different event types. 

Thanks,

Robert D. Specian Jr.


----------



## verobio (Oct 20, 2011)

Thank you Robert! However I forgot to say I am using excel 2003, can you tell me where is the "record macro" button?

Thanks!


----------



## verobio (Oct 20, 2011)

don't worry, I found the "record macro" I will try it.


----------



## verobio (Oct 20, 2011)

wow, this is great, it works! One more question, now I can't write anything in other cells of the worksheet where my data are automatically being sorted, is there something we can do to fix this so that i can write in other cells of the same worksheet?


----------



## RSpecianjr (Jan 20, 2010)

I'm not sure what you are saying... haha Can you give a little more detail?


----------



## verobio (Oct 20, 2011)

well, the data from worksheet 2 that are linked to data on worksheet 1 are being automatically sorted, which is very good and what I wanted. However, those cells being sorted out in worksheet 2 are still highlighted/selected (probably because of the "record macro" I did as the first step) and I am not able to write in other cells of that worksheet...It seems like it now doesn't want to unselect the columns sorted...weird...can you help me out?


----------



## RSpecianjr (Jan 20, 2010)

Can you post the code from your macro?


----------



## verobio (Oct 20, 2011)

' Macro1 Macro
' Macro recorded 20/10/2011 by Véronique Lalande
'
'
Range("B2:C101").Select
Selection.Sort Key1:=Range("C2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("D4").Select


----------



## RSpecianjr (Jan 20, 2010)

Hey Veronique,

I knew one other Veronique, she was a french teacher in my middle school. Great name .

There isn't anything in this code that would cause you to not be able to change anything. My guess is that it is the type of event you are using. What is the header to the macro?

Regards,

Robert D. Specian Jr.


----------



## verobio (Oct 20, 2011)

what do you mean by header? 

this? Sub Macro1()


----------



## RSpecianjr (Jan 20, 2010)

Yeah, whatever is right above the:

' Macro1 Macro


----------



## verobio (Oct 20, 2011)

this all what is in the code box for sheet 2:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
' Macro1 Macro
' Macro recorded 20/10/2011 by VL
'
'
Range("B2:C101").Select
Selection.Sort Key1:=Range("C2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub


thanks so much for your help


----------



## RSpecianjr (Jan 20, 2010)

change:
selectionchange
to:
change


----------



## RSpecianjr (Jan 20, 2010)

Basically, what you was happening is that when you change the cell selection, it ran your macro. Now, when you change a cell value and hit enter or tab, it will run the macro.


----------



## verobio (Oct 20, 2011)

sorry to say this but it is not sorting anymore.... :-(

is there a way that I can attach the file?


----------



## RSpecianjr (Jan 20, 2010)

You will have to enter data in for it to sort


----------



## verobio (Oct 20, 2011)

hum....I tried to update some data and it didn't sort....


----------



## RSpecianjr (Jan 20, 2010)

Will you zip the worksheet and upload it to a post = )


----------



## verobio (Oct 20, 2011)

Here is the file in zip

Thanks!


----------



## RSpecianjr (Jan 20, 2010)

Hey verobio,

Try this instead:



> Private Sub Worksheet_Change(ByVal Target As Range)
> '
> ' Macro1 Macro
> ' Macro recorded 20/10/2011 by VL
> ...


And move the macro from sheet the Final Ranking worksheet to the Scoring Grid.

Regards,

Robert D. Specian Jr.


----------



## verobio (Oct 20, 2011)

we are close... ;-)

so where do I copy the quote? in visual basic under sheet 1 or 2? If 2, do I remove the one I had?

And what do you mean by moving the macro from sheet the Final Ranking worksheet to the Scoring Grid?

Sorry I am definitely a beginner... ;-)


----------



## RSpecianjr (Jan 20, 2010)

Hey,

No worries, we all start somewhere. When you have the VBA editor open and you are running an event macro, the event itself is for that worksheet. So, if you have a Worksheet_Change macro under the Final Ranking worksheet it will run when something changes in the Final Ranking Worksheet. 

That being said, I am guessing you want it to auto sort when you change something on the Scoring Grid Worksheet.... so you would paste the code into the Scoring Grid Worksheets area...

To give directions:

1. Copy the macro I posted above.
2. Open your workbook.
3. Right click on the Scoring Grid Tab at the bottom and click View Code.
4. Paste the copied macro in there.
5. Remove the macro from the Final Ranking Worksheet.


Let me know if this works for you.

Regards,

Robert D. Specian Jr.


----------



## verobio (Oct 20, 2011)

thanks. hum after doing what you sais, there is this error message poping when I try to enter data in sheet 1, for example trying to give score to a project:

run-time error 438; object doesn't support this property or method


----------



## RSpecianjr (Jan 20, 2010)

Does it offer to debug it? What version of excel are you using? Does it highlight a specific line of code?


----------



## verobio (Oct 20, 2011)

yes it offers to debug it, and when I debug it, here is what's highlighted:

ActiveWorkbook.Worksheets("Final Ranking").Sort.SortFields.Clear

I am using excel 2003.

Here's the file, you will see that the message error pops-up when we try to change data in sheet 1:


----------



## RSpecianjr (Jan 20, 2010)

Hey verobio,

It works fine for me, but I have Excel 2007... can't test in in 2003, perhaps someone else on here can test it?

Regards,

Robert


----------



## Boy_excel (Nov 15, 2011)

Hello Robert,

I have had same problem to sort data in Excel 97-2003.

1.First,the data can be transferred from sheet 1 to sheet 2.But the data can't be sorted automatically in sheet 2. 

2. The data in the top cell can't be sorted .e.g.



The below is the Macro:- Could you please point where the wrong is?

Sub sort()
'
' sort Macro
'
'
Range("G9:G16").Select
Selection.AutoFilter
ActiveSheet.Range("$G$9:$G$16").AutoFilter Field:=1, Criteria1:=">0", _
Operator:=xlAnd
ActiveWorkbook.Worksheets("Sheet2").AutoFilter.sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet2").AutoFilter.sort.SortFields.Add Key:=Range _
("G9"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet2").AutoFilter.sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

thank you


----------



## RSpecianjr (Jan 20, 2010)

Hey boyexcel,

Either change Header = xlyes to no or extend the range up one row to cover G8:G16.

Regards,

Robert D. Specian Jr.


----------



## Boy_excel (Nov 15, 2011)

Thank you Robert.The topic has moved to http://www.techsupportforum.com/forums/f57/cant-sort-in-excel-automatically-612822.html


----------

