XLAutomation Newsletter

If you experience difficulties viewing this email. Click Here to view the Newsletter in your browser

Create your own Macro's (Part 3)
by Scott Lindsay (22 May, 2009)

Macro's are a powerful tool within Excel that can allow you to perform complex tasks with the click of a button. A Macro is simply a series of commands recorded into a script that is executed when required.

This month we develop a Macro that will extend that basic Data Sort functionality that is provided by Excel 2003. For users of Excel 2007 the Data Sort functionality has been significantly enhanced and the Macro we are building in this month's Newsletter is probably not needed.

If you have a Macro suggestion for inclusion then Contact Us and we will endeavour to include it in a future newsletter.

Extended Data Sort

Let's say that you have a list of Customer's and their most recent Orders placed for your Products. You want to be able to Sort the list by Country, by State, by Suburb by Product by Transaction Date, descending.

Within Excel 2003 that type of Sort is not possible through a single command. We will now develop a Macro that will allow you to Sort by the required columns.

Step 1 - Download and Open the Sample Workbook. Access Sheet1 to show the list of Customers we are about to create the Sort for.

Step 2 - Now select the cell containing the Column name "Transaction Date" and then select and then Select Insert -> Name -> Define. Type in the name "TransactionDateStartHeading" and click OK. This will provide a reference point for our Macro when we start Sorting the table.

Step 3 - Select Tools -> Macro -> Visual Basic Editor. A new window will be opened that allows for the direct entry and creation of Macro's. Select the VBA Project of the Workbook you created in Step 1 above.

Now Select Insert -> Module. A new Module will be created for the Workbook if this is the first Macro in the Workbook it will be called Module1

Step 4 - We are now ready to create our Sorting Macro. Right Click on Module1 and Select View Code. On the Right Hand Side of the Visual Basic Editor a blank Code window will be displayed.

Step 5 - Copy the following Macro Code into the Visual Basic Editor, this will create the Macro within your Workbook.

Sub ExtendedSort()
'This Macro will Sort the Table of data
'by Country, by State, by Suburb, by Product, by Transaction Date descending

Dim myRange                             As Range
Dim i                                   As Integer

    Set myRange = Worksheets("Sheet1").Range("TransactionDateStartHeading")
    Set myRange = Range(myRange, myRange.End(xlToRight))
    Set myRange = Range(myRange, myRange.End(xlDown))

    'First sort by Product, by Transaction Date desc
    With myRange
        .Cells.Sort Key1:=.Columns(3), _
                    Order1:=xlAscending, _
                    Key2:=.Columns(1), _
                    Order2:=xlDescending, _
                    Header:=xlYes
    End With

    'Now sort by the remaining required values
    'by Country, by State, by Suburb
    With myRange
        .Cells.Sort Key1:=.Columns(8), _
                    Order1:=xlAscending, _
                    Key2:=.Columns(7), _
                    Order2:=xlAscending, _
                    Key3:=.Columns(6), _
                    Order3:=xlAscending, _
                    Header:=xlYes
    End With

    MsgBox "Sort now Complete for Sales Data", vbInformation, "Successful Sort"

End Sub

Step 6 - Close the Visual Basic Editor Window and we will now run the Macro to Sort the Customer data. Select Tools -> Macro -> Macros. Then Select the Macro we just created in Step 5 above 'ExtendedSort', then Select Run.

Re-format Customer Name

This should then run the Macro and Sort the Customer Data into the required order. The Re-Sorted list should look something like the listing below.

If you received an error message when running the Macro check that you defined name is exactly the same as the one specified in Step2 above.

If the Macro is still reporting errors then download the Extended Sort Complete Workbook and see this Macro in action Click Here.

If you have any questions or would like help in developing Macro's for your business then Contact Us and we will assist.

If you are not yet a subscriber to the XLAutomation newsletter then Click Here to subscribe and receive guidance and tools that will increase your efficiency in using Microsoft Excel, Word and Powerpoint.

Breaking News...
We are now offering Excel Training for business on the Sunshine Coast. Click Here for details on our Courses

Subscribe to WIN...
Click Here for details on how Subscribers can enter the next competition for your chance to win more FREE consulting from XLAutomation team

Get Free Advice...
If you have a specific problem or area of Excel that you would like to see featured in next months Newsletter then let us know. Click Here to tell us what YOU need.

Latest Offers
"Free Consultation"
Take advantage of our 1 hour free on-site consultation. We guarantee to provide advice that will improve the way you currently use your Microsoft® Office software. Click Here to book a session with one of our consultants.