Solved

Excel 2013 Autosort with VBA

Posted on 2014-10-30
4
337 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 48

Expert Comment

by:Rgonzo1971
Comment Utility
Hi,

maybe by changing

on line 6

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

Regards
0
 
LVL 16

Author Comment

by:Dirk Mare
Comment Utility
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 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
Comment Utility
In which module is your code

it runs from the thisWorkbook module
0
 
LVL 16

Author Comment

by:Dirk Mare
Comment Utility
Thank You its working I changed it to ThisWorkbook..
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
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…

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now