Solved

Excel 2013 Autosort with VBA

Posted on 2014-10-30
4
353 Views
Last Modified: 2014-10-30
Hi Experts,

I want to sort me Excel 2013 Sheet Automatically when opening the workbook.

I have a couple of sheets within the workbook but I only want to auto sort the one. "All Devices"

Sheet details..
Row A4:AA4 has the Sheet Headers

Row A5:AA3003 Sheets data (to be sorted)

I want to sort all data from
A5 to AA3003
Sort Column A (Location)
And then Column B (Type)

Screenshot of manual sort attached.
manual sort data
Private Sub Workbook_open()
    
    Range("A4:AA3003").Select
        ActiveWorkbook.Worksheets("All Devices").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("All Devices").Sort.SortFields.Add Key:=Range( _
            "A4:AA3003"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        ActiveWorkbook.Worksheets("All Devices").Sort.SortFields.Add Key:=Range( _
            "B4:B3003"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        With ActiveWorkbook.Worksheets("All Devices").Sort
            .SetRange Range("A4:AA3003")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
End Sub

Open in new window


Thanks
0
Comment
Question by:Dirk Mare
  • 2
  • 2
4 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40412985
Hi,

maybe by changing

on line 6

"A4:AA3003"),
to
 "A4:A3003"),

Regards
0
 
LVL 16

Author Comment

by:Dirk Mare
ID: 40413015
Thank You I can run the code from Visual Basic Application now, but it doesn't work when opening the workbook.

I think it might be this string..

Private Sub Workbook_open()

Open in new window


Thanks
0
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40413027
In which module is your code

it runs from the thisWorkbook module
0
 
LVL 16

Author Comment

by:Dirk Mare
ID: 40413090
Thank You its working I changed it to ThisWorkbook..
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question