Sorting Data in Workbook2 from Workbook1

Question

I'm not sure why the code below isn't working. I'm using this as a part of a larger VBA sub, but I'll just post the relevant code below

I want to sort a range (by A to Z) on a separate Workbook. The range is "A5:M600" and the worksheet is "Leaders". This is stored on the Workbook declared as 'wb2'.

The code below will get as far as opening the file where I want to execute the sort, select the range I want to sort, but it won't actually sort the selection.

Any ideas?

Sub SortWB2()
Dim wb2 As Workbook
Dim RetFilePath

'~~> Get the file path
RetFilePath = "T:\Purchasing\ADVENTURE RMS\Data Files\2015\Data.xlsx"

'if file path is not found, then exit the sub below
If RetFilePath = False Then Exit Sub

'set wb2 to open the file
Set wb2 = Workbooks.Open(RetFilePath)


With wb2.Worksheets("Leaders").Sort
    .SetRange Range("A5:M600")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Application.DisplayAlerts = False
wb2.Close SaveChanges:=True
Application.DisplayAlerts = True


Set wb2 = Nothing

End Sub

If you step through the sub (with F8), when it gets to `.Apply` nothing happens, correct? Also, what if you change your `With` statement to `With wb2.worksheets("Leaders").Sort`? If you declared the workbook (as you did, wb2), then use that instead of using `Activeworkbook`, as it would help in the long run to cut down on bugs. — BruceWayne, Jul 24 '15 at 14:21
Thanks for reply, when pressing through with F8 nothing happens. Also, I did originally have wb2.worksheets instead of Activeworkbook.Worksheets. I'll change the above, I was trying different things to figure out what's going on — arbitel, Jul 24 '15 at 14:27
I think it might also be because your `.SetRange` isn't explicitly setting the range on the "Leaders" worksheet, I think if you make that explicit, it'll work. — BruceWayne, Jul 24 '15 at 14:29

score 2 · Accepted Answer · edited May 23 '17 at 12:06

2

Try to stay away from .Activate and .Select as ways to direct the target of your code.

Set wb2 = Workbooks.Open(RetFilePath)

With wb2.Worksheets("Leaders").Range("A5:M600")
    .Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
                Key2:=.Columns(3), Order2:=xlDescending, _
                Orientation:=xlTopToBottom, Header:=xlNo
End With

That will sort on column A as the primary key then column C as the secondary key. You can remove the secondary key if it is not needed. You can add a third key (e.g. Key3:=.Columns(14), Order3:=xlAscending for column N ascending) but it has a maximum of three keys. You can double up the command if you require more.

See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

edited May 23 '17 at 12:06

Community

1
1

answered Jul 24 '15 at 14:31

Nice, I forgot that you can do the `Key1` thing. Good thinking. – BruceWayne Jul 24 '15 at 14:32
Thank you! This worked like a charm and sorted the whole selection perfectly A to Z based on the first column. Never sorted data this way, will do some research :) – arbitel Jul 24 '15 at 14:41
1

@Jeeped - do you know what might be causing the original loop not to work (or what I thought would work, below)? While it's good to avoid `.activate` and `.select`, it should still work, no? Or is it just not going to work, despite that it "should", because `.activate` can be finicky? – BruceWayne Jul 24 '15 at 14:48
1

Recorded macro Sort code comes in three parts; a) remove previous sort defaults b) set one or more keys and finally c) set the range and other properties then apply the sort. You really only had the last one. Record a sort operation to see what the recorder thinks you need. – Jul 24 '15 at 15:01

score 0 · Answer 2 · answered Jul 24 '15 at 14:28

0

Hm, it may be that you should be explicit with your ranges. Try this for the With statement:

With wb2.Worksheets("Leaders").Sort
    .SetRange wb2.worksheets("Leaders").Range("A5:M600")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

I think (hope!) that does it.

answered Jul 24 '15 at 14:28

BruceWayne

22,923
15
65
110

It yields the same result, it will open the file and select the range in the Leaders worksheet but doesn't execute the sort – arbitel Jul 24 '15 at 14:33

Sorting Data in Workbook2 from Workbook1

2 Answers2