Sort When Saving

Question

Is there a way to set up an Excel workbook (or Excel in general) to automatically Sort based on the last Sort criteria when a Workbook is saved?

Answer 1

Debbie2297, 

Do Alt+F11 to get to the Visual Basic Editor

in the project explore on your left, find the entry for ThisWorkbook for you project.   This is where workbook level event code is placed.  Right click on the thisworkbook entry and choose view code.

in the resulting module, there will be two dropdowns at the top.  In the left dropdown select  Workbook

in the right dropdown select  BeforeSave.  This should put a the beforesave event in the module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

End Sub

put in code like this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

With Worksheets("Sheet1")

  .Range("A1:M200").Sort Key1:=.Range("C1")

End With

End Sub

This sorts the range A1:M200  using column C.   If you don't need to change any of the settings, then just remove the Key1 argument so it looks like:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

With Worksheets("Sheet1")

  .Range("A1:M200").Sort

End With

End Sub

If that doesn't work for you, under the developer tab you can turn on record macro and then manually sort your data.  Then turn the macro recorder off and see what the recorded code looks like.  The sort method I used is the old method because it is very simple.  You will probably record the new method which is more complex. 

By default, not providing any arguments to the old Sort method should cause it to inherit the existing settings since they are persistent.

In any case, change the sheet name name and range to sort to reflect the range in your workbook where you want this to occur.

--

Regards,

Tom Ogilvy

Answer 2

That helps a little.  I am not a programmer, and my workplace Help desk does not support Office.  So, I will need a few more details.  I understand very little about typing in code.  I found the Developer, I found Visual Basic Editor and I have been searching for something to copy and paste.  I see the BeforeSave information but I am not sure how to go about entering a VBA code.  Any more help would be greatly appreciated.

Thanks,

Debbie

Answer 3

Debbie2297,

You can use the before save event to run a the vba code to sort your data.

The before save event is a workbook level event and found in the ThisWorkbook module in the Visual Basic Editor.

--

Regards,

Tom Ogilvy

--

Regards,

Tom Ogilvy

.