Automatic Sorting of Sheet Based on One Column Value

meanwhilecreative

New Member
Joined
Mar 11, 2014
Messages
5
As the title suggests, I'm trying to sort a sheet of data based on a single column, and then have further entries automatically sort. The data has columns A-L, I would like it sorted based on column K, discounting the headers which are in row 1. The values in column K are 1,2 or 3 and there are be many ties or duplicates.

If anyone can help I would be most appreciative.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,138
Office Version
2013
Platform
Windows
OK, the sort is simple enough.....care to give us more information ?
Or paste a small sample of the data, and what you actually want to achieve beyond the basic sort of column K
 

meanwhilecreative

New Member
Joined
Mar 11, 2014
Messages
5
OK, the sort is simple enough.....care to give us more information ?
Or paste a small sample of the data, and what you actually want to achieve beyond the basic sort of column K
Thanks for replying.

The HTML Maker link seems to be down so I'm hoping I can explain.

The sheet is designed to log enquiries fielded to my place of work. The enquiries have many pieces of information such as date of enquiry, contact details etc. which are all column headings from A through to L. These enquiries (i.e. the row) should be ordered based on the data in column K, this is their classification as active, inactive or on hold, as represented by numbers 1, 2 or 3. I will be adding new enquiries to this document and changing the classification of existing enquiries and would like them to sort automatically based on this.

Is there any more information I can give? Any help would be much appreciated, I am only just beyond the very basics of Excel.

I am using Excel for Mac 2011, Version 14.3.8
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,138
Office Version
2013
Platform
Windows
Maybe this.
It will resort the data when the Save Button is clicked
Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Range("A2:L" & Cells(Rows.Count, "K").End(xlUp).Row).Sort Key1:=Range("K2"), Order1:=xlAscending, Header:=xlYes
End Sub
 

meanwhilecreative

New Member
Joined
Mar 11, 2014
Messages
5
Maybe this.
It will resort the data when the Save Button is clicked
Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Range("A2:L" & Cells(Rows.Count, "K").End(xlUp).Row).Sort Key1:=Range("K2"), Order1:=xlAscending, Header:=xlYes
End Sub
This hasn't made any difference sadly. Thanks for your help all the same.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,138
Office Version
2013
Platform
Windows
did you get an error ??
Where is the code pasted ?
What did or didn't the code do ??

This hasn't made any difference sadly
This won't help us help you solve the problem !
 

meanwhilecreative

New Member
Joined
Mar 11, 2014
Messages
5
did you get an error ??
Where is the code pasted ?
What did or didn't the code do ??



This won't help us help you solve the problem !
Apologies. I tried the code in two places - once via the Record function under the developer ribbon, and then via the editor tool having gone to 'view code' on the sheet in question. I tried both of these and deleted the other when doing so.

There was no error message, nothing happened whatsoever. Closed and reopened the program and such like.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,138
Office Version
2013
Platform
Windows
You did click SAVE when you had finished the data input didn't you ??
 

golf4

Active Member
Joined
Jul 8, 2002
Messages
452
Hi, Michael -
I saw your replies to this post, and would like to ask a follow-up question:

I have the same issue as the original poster - sorting data on one sheet. I already have the code below included in the Workbook, but was wondering how I could incorporate your code into mine. The data I need sorted is included on Sheet2 via Column H descending. I've tried a couple times, and keep getting errors. Any help you can provide would be great!!!

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("sheet1").Unprotect ("led52not")
Sheets("sheet1").Columns("A:AB").Clear
Sheets("sheet1").Protect ("led52not")
Sheet2.Columns("A:AB").AutoFilter
Sheet2.Columns("A:AB").AutoFilter
End Sub


Thanks again.

Frank
 

Forum statistics

Threads
1,081,556
Messages
5,359,547
Members
400,533
Latest member
fpenning

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top