Automatic resort of data

Microsoft Excel 2003 (full product)
February 12, 2010 at 07:32:52
Specs: Windows XP
I want to set up a macro, that automatically resort your data alphabetically when saving, can anyone help.



See More: Automatic resort of data

Report •

February 14, 2010 at 01:09:32
Fay maitland, while you may well find an answer here, suspect you'll find more knowledgeable folks in the programming forum @:
Ed in Texas.

Report •

February 15, 2010 at 10:30:51
A better forum would be Office, so I'll send this thread over there.

Report •

February 15, 2010 at 11:10:22

Its not clear from your question how familiar you are with macros.

Just as an outline approach - you can use the Workbook 'Before Save' event to trigger a macro to sort your data.

The macro uses Excel's sort function something like this:
Worksheets("Sheet1").Range("A1:D20").Sort _
Key1:=Worksheets("Sheet1").Columns("A"), _
Header:=xlYes, _

If you want to provide more details - worksheet name, range of cells to be sorted, header row if any, we may be able to provide more help.

Also advice on how to put a macro in the 'before save' event can be provided if required.


Report •

Related Solutions

February 15, 2010 at 23:56:05
thanks for the above information, the work sheet name is 2010 onwards, the cells to be sorted are a2:bq216, and to be sorted by month which is column P, the header row is a1 to bq1

hope this is enough



Report •

February 16, 2010 at 00:13:21
I am not very knowledgeable on Macros, so a explanation on how to put them in would be much appreciated.



Report •

February 16, 2010 at 06:35:35

Here is the macro code for running a sort on the date in column P on a sheet called '2010 onwards' with data in cells a2:bq216.
Row 1 contains the headings
Column P is column #16 and contains the date to be sorted on.

As macros cannot be undone using the Undo function and as this macro has not been tested in your environment on your data, test this on a copy workbook. Also always make a backup copy of your data.

The macro is attached to the Workbook object, as it is the workbook that is saved
I named the workbook 'PreSaveSort.xls'
The actual name does not matter.

With your workbook open, click Alt+f11 (the Alt key and function key #11 together)
This opens the visual basic window (macros are written in a modified type of visual basic called visual basic for applications (VBA))

On the left of the VB window is the Project Explorer window - if it is not visible goto the VB menu bar and select View - Project Explorer

You will see the name of your workbook like this VBAProject(PreSaveSort.xls)
Underneath find ThisWorkbook in the Microsoft Excel Objects group
Double click ThisWorkbook

In the large window on the right paste the following into the window

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim strPath As String
Dim strFileFullName As String
Dim strFileName As String
Dim strFileExt As String

'stop events so that this code is not triggered again when saving backup
Application.EnableEvents = False

On Error GoTo ErrHnd

'path to save folder - must end with \
strPath = Application.ActiveWorkbook.Path & "\"

'this file's name and file extension
strFileFullName = Application.ActiveWorkbook.Name
strFileName = Left(strFileFullName, _
        InStr(1, strFileFullName, ".") - 1)
strFileExt = Right(strFileFullName, Len(strFileFullName) - _
        InStr(1, strFileFullName, "."))

'save a backup copy
Workbooks(strFileFullName).SaveCopyAs Filename:=strPath & strFileName & _
        "_bak" & strFileExt

Worksheets("2010 onwards").Range("A1:BQ216").Sort _
        Key1:=Range("P1:P216"), _
        Order1:=xlAscending, _

're-enable events
Application.EnableEvents = True
Exit Sub

'error handler
Application.EnableEvents = True
MsgBox "There was an error in the pre-save sort"
End Sub

If you click on the line 'Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)' or a line below it, the two boxes above the window should show Workbook and BeforeSave.
If not, the code is not in the right place.

Click Save from the VB menu
Use Alt+f11 to go back to the main Excel window.

Clicking save should save a copy of the file (same folder same name but '_bak' inserted before the file extension)
Then it sorts on column P
and finally the file is saved as normal.

If there is an error in the code, or for some reason the sort fails, you will get an error message to warn you that there is a problem

I don't know if the sort will work 'as expected' as I don't have your data to test it on. If it doesn't sort as expected, please post a few rows from column P, both unsorted and as required after sorting.


Report •

Ask Question