Sorting data to Workbook2 from Workbook1
I am not sure why the below code is not working. I am using this as part of a larger VBA module, but I will just post the relevant code below
I want to sort a range (A to Z) in a separate book. The range is "A5: M600" and the worksheet is "Leaders". This is saved in a workbook declared as "wb2".
The code below will expand the file I want to sort on, select the range that I want to sort, but it will not 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
source to share
Try to stay away from .Activate
and .Select
as ways of specifying the purpose 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
This will sort by column A as primary key and then column C as secondary key. You can delete the secondary key if you don't need it. You can add a third key (for example Key3:=.Columns(14), Order3:=xlAscending
for column N in ascending order), but it has a maximum of three keys. You can double the team if you need more.
See How to avoid using Select in Excel VBA macros for more methods to avoid using Select and Activation to achieve your goals.
source to share
Hm, maybe you should be explicit with 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 (hopefully!) Who does it.
source to share