How does 'sort' work while recording a macro ?

Jai Popat · July 7th, 2015, 05:54 AM

Hi,

Am a first time user of VBA and am still at chapter 2 of the book learning to use the Macro Recorder. I have been trying to work out the example at pg.16 where the macro code requires sorting by way of Region , items and count. I followed all the steps laid out in instructions in this example to record the macro. The Macro does work except for the sort. i.e. The count col formatting works , the new col creation works, the old col deletion works and bold formatting works. But the macro does not sort. Is this a standard issue or am I missing something here ?

Tom Urtis · July 10th, 2015, 08:17 PM

Hi, thank you for buying my book.

The instructions on page 17 explain how to sort the 3 fields. You can change the sequence of the sorting columns, or only have one column be sorted.

If you're still having a problem, please post back.

Tom

FrustratedUser · January 1st, 2016, 12:11 PM

I am having the EXACT same problem and have spent countless hours trying to figure it out with no satisfaction!

1) The only thing the Instructions on page 17 say is: "Select Range A1:C13 and sort in Ascending order by Region Item and Count"

2) I perform the following actions while the macro record is active:

a) use mouse to select cells A1:C13
b) Click Sort, Sort by Region, Add Level, Sort by Item, Add Level, Sort by Count, Largest to Smallest

3) When I perform these actions, the table is sorted as intended

4) However, when I run the macro on a test table, the formatting works perfectly, but the data is unsorted!

Is there some kind of trick with using the sort function while recording a macro?

FrustratedUser · January 1st, 2016, 12:21 PM

Also ... tried it with sorting on a single variable (Region) ... still didn't work

Tom Urtis · January 1st, 2016, 01:55 PM

Hi, and thanks for buying the book.

Something is affecting the results you are getting (or not getting) that, based on your description, I cannot replicate.

Just now, I created this example table in A1:C3, with A1:C1 being header cells:

State Items Count
Texas Socks 33
Calif Shirt 86
Idaho Shirt 37
Calif Pants 83
Idaho Shirt 93
Idaho Shirt 60
Calif Pants 31
Texas Shirt 95
Idaho Socks 66
Texas Pants 33
Calif Shirt 86
Calif Socks 11

I used the macro recorder and got this code:

Sub Macro1()
Range("A1:C13").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("A2:A13") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("B2:B13") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("C2:C13") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:C13")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

Then, I clicked the Undo button to restore the original data. I ran the recorded macro and it produced this sorted table, as expected:

State Items Count
Calif Pants 83
Calif Pants 31
Calif Shirt 86
Calif Shirt 86
Calif Socks 11
Idaho Shirt 93
Idaho Shirt 60
Idaho Shirt 37
Idaho Socks 66
Texas Pants 33
Texas Shirt 95
Texas Socks 33

Finally, I had made a copy of the original data and pasted it onto the sorted data in the original range, and tested the recorded macro again. Same result, sorted as I recorded.

Maybe I am missing something about what is being seen by yourself and the first poster on this thread. Or, just thinking out loud, is the workbook saved as a .xlsm (for macros to be executed, as opposed to .xlsx for code-less workbooks), or are your macro's security settings allowing for your macro to run.

In any case, please post back with what I am missing. Or if you like, you can email me directly with your attached workbook (my email address is in the "About the Author" section near the front of the book), and I'll be happy to take a look at what you are seeing.

FrustratedUser · January 1st, 2016, 06:29 PM

Okay ... so I think I figured out what the problem is/was ...

I haven't reached the part of the text on reading/debugging the code yet, but here is what I figured out.

Within the same workbook, I had used one worksheet (Sheet 1)to develop (record) the macro and another worksheet (Sheet2) to test the macro on the original data. [So I wouldn't have to re-enter or re-paste the data every time I tested the macro]

Apparently inserting and deleting columns, cutting and pasting & formatting are "transportable" from sheet to sheet within the workbook. That is why these functions worked.

However, the sorting macro clearly requires the sheet to be sorted to be identified. That portion of the macro (and only that portion) failed to work when I ran the macro on Sheet 2.

I'm guessing at some point in the course I will learn how to adjust the code so that the sort feature is "transportable" from one Sheet to another within the same workbook. Not sure how to do this with "record macro" yet ... perhaps selecting the active sheet while recording the macro will do it.

Thanks for your help

Tom Urtis · January 1st, 2016, 07:56 PM

As you will see later in the book, where VBA is concerned, the parent object (in this case, the worksheet where the sort should take place) must be defined. VBA needs to know which worksheet you want to affect, in case the worksheet you are currently on (that is, the active sheet), is not the worksheet where the data needs to be sorted.

The code to take that worry away from you, where range A1:C13 is sorted regardless of the current worksheet, assuming your workbook with that range to be sorted is the active workbook (you'll see how to deal with multiple workbooks, too) can be like this:

Sub Macro2()
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("A2:A13") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("B2:B13") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add Key:=Range("C2:C13") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:C13")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

And that macro can be further reduced to this. Notice the period (or dot) characters in front of ranges, inside what are called With structures, as you will see in the book how to construct. Here's an advance look that tested fine for me:

Sub Macro3()
With Worksheets("Sheet1")
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=.Range("A2:A13"), SortOn:=xlSortOnValues, Order:=xlAscending
.Sort.SortFields.Add Key:=.Range("B2:B13"), SortOn:=xlSortOnValues, Order:=xlAscending
.Sort.SortFields.Add Key:=.Range("C2:C13"), SortOn:=xlSortOnValues, Order:=xlDescending

With .Sort
.SetRange Range("A1:C13")
.Header = xlYes
.Apply
End With

End With
End Sub