Excel VBA macro to sort alphabetically before save

Question

In Excel, I'm trying to enter data in a new row, and when I save, have it automatically sort all populated rows alphabetically by the values in column A (essentially, I want to remove the hassle of clicking "Sort A to Z" before each save). I have the following in the code of the worksheet (the workbook contains three worksheets, and I only want this macro to operate for one of them)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    On Error Resume Next
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        Range("A1").Sort Key1:=Range("A3"), _
          Order1:=xlAscending, _
          MatchCase:=False, _
          Orientation:=xlTopToBottom
    End If 
End Sub

Note: The key1 range starts at A3 because rows 1-2 are headers. Can anyone figure out why this code isn't working? I don't really code so if something's missing I would have no idea. I know it's not an issue with macros being enabled because I can run other macros manually with no problem, it's just this automatic one that isn't working.

Did you place this code in the `ThisWorkbook` section ? It's an event, so it needs to be there and not in a custom module/sheet.https://stackoverflow.com/questions/12365417/excel-vba-run-macro-before-save for reference — Jean Rostan, Apr 09 '18 at 21:51
It's not working because there is no target. Yo9u copied too much code and you're using on error resume next. — , Apr 09 '18 at 21:56

score 0 · Answer 1 · answered Apr 09 '18 at 22:14

0

It's not working because there is no target. You copied too much code from wherever you got this from and you're using on error resume next so any errors is simply skipped over.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    with thisworkbook.worksheets("sheet1")
        with .cells(1, "A").currentregion
            .cells.offset(1, 0).Sort Key1:=.columns(1), Order1:=xlAscending, _
                                     Orientation:=xlTopToBottom, header:=xlyes
        End with
    End with
    SaveAsUI  = false
    Cancel = false
End Sub

answered Apr 09 '18 at 22:14

I tried using your code but it didn't work either. I also tried the code from Jerry's answer, and at least that gave me a runtime error, but the code you provided just didn't run at all. – ETL Apr 11 '18 at 04:38
Update: After closing the file, reopening it later, and trying your code again, it now returns runtime error '9'. Debugging points to the same line, "with thisworkbook.worksheets("sheet1")," but I have no idea what's wrong with that. changing the wording to "ThisWorkbook.Sheets("Sheet1")" makes no difference. – ETL Apr 11 '18 at 06:15
@ETL - the valid example above uses a fully qualified range reference and assumes you have a worksheet named e.g. "Sheet1"; if you don't have such a sheet, you'll get an Error 9 "Subscript out of range" because Excel doesn't find this string in the worksheets collection. Just change the sheet name in `ThisWorkbook.Worksheets("Sheet1")` to your actual name. – T.M. Apr 11 '18 at 08:48

score 0 · Answer 2 · answered Apr 09 '18 at 22:42

0

This macro will sort the range you have specified. You will need to edit the bottom cell range if you need it to go lower than A12

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)

With ThisWorkbook.Worksheets("Sheet1")
Dim LastRow As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A3:A12" & LastRow).Sort Key1:=Range("A3:A12" & LastRow), _
       Order1:=xlAscending, Header:=xlNo
End With

End Sub

answered Apr 09 '18 at 22:42

Jerry

100
2
9

Tried this code, got a runtime error saying the subscript was out of range. Apparently a problem with the line: With ThisWorkbook.Worksheets("Sheet1"), although I don't know why that would be – ETL Apr 11 '18 at 04:31

score 0 · Accepted Answer · answered Apr 11 '18 at 07:05

Figured out the issue with the two previous responses was that I had renamed my sheets, so I needed to change ThisWorkbook.Worksheets("Sheet1") to Sheet1.

In the end, I wanted to alphabetize two of the three sheets in the workbook whenever I saved the workbook, so with the help of the Record Macro function to figure out what the hell I was doing, I ended up with the following code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.ScreenUpdating = False
Sheet1.Sort.SortFields.Clear
    Sheet1.Sort.SortFields.Add Key _
        :=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With Sheet1.Sort
        .SetRange Range("A:D")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Sheet2.Sort.SortFields.Clear
        Sheet2.Sort.SortFields.Add Key _
            :=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        With Sheet2.Sort
            .SetRange Range("A:D")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    SaveAsUI = False
    Cancel = False
Application.ScreenUpdating = True
End Sub

Note: I couldn't figure out how to specify the header as rows 1 AND 2, so I just put a leading space in A2's value so that row 2 would always be sorted as first. — ETL, Apr 11 '18 at 07:09

Excel VBA macro to sort alphabetically before save

3 Answers3