Calling another workbook

[Patrick Simonds]

The code below is run by clicking on a UserForm button which is part of
WorkBook Vacation.xls. Is there any way to perform this with out making
Employee List.xls active or to return me to where I was in Vacation.xls
after the sort is complete?


Sub PT_Driver_Last_Name_Sort()
'
' Macro1 Macro
' Macro recorded 12/20/2005 by Cathy Baker
'
'Sort by Paratransit Drivers Last Name

Windows("Employee List.xls").Activate
Worksheets("Employee_List").Range("AA1").Value = 2

Dim wks As Worksheet
Set wks = Worksheets("Employee_List")
With wks.Range("A1:Z300")
..Sort Key1:=wks.Range("E2"), Order1:=xlAscending, Key2:=wks.Range("F2") _
, Order2:=xlAscending, Key3:=wks.Range("A2"), Order3:=xlAscending,
Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:=
_
xlSortNormal
End With

End Sub

 

[Guest]

Remove this line:

Windows("Employee List.xls").Activate


Sub PT_Driver_Last_Name_Sort()
'
' Macro1 Macro
' Macro recorded 12/20/2005 by Cathy Baker
'
'Sort by Paratransit Drivers Last Name


Worksheets("Employee_List").Range("AA1").Value = 2

Dim wks As Worksheet
Set wks = Worksheets("Employee_List")
With wks.Range("A1:Z300")
...Sort Key1:=wks.Range("E2"), Order1:=xlAscending, Key2:=wks.Range("F2") _
, Order2:=xlAscending, Key3:=wks.Range("A2"), Order3:=xlAscending,
Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:=
_
xlSortNormal
End With

End Sub

 

[Guest]

Lets try again -- I hit send too soon.

Sub PT_Driver_Last_Name_Sort()
'
' Macro1 Macro
' Macro recorded 12/20/2005 by Cathy Baker
'
'Sort by Paratransit Drivers Last Name

Dim bk as Workbook
set bk = Workbooks("Employee List.xls").
bk.Worksheets("Employee_List").Range("AA1").Value = 2

Dim wks As Worksheet
Set wks = bk.Worksheets("Employee_List")
With wks.Range("A1:Z300")
.Sort Key1:=wks.Range("E2"), Order1:=xlAscending, _
Key2:=wks.Range("F2"),Order2:=xlAscending, _
Key3:=wks.Range("A2"), Order3:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
End With

End Sub

 

[Patrick Simonds]

The problem with that is that Vaction.xls is the active workbook and this
code is performing a sort on Employee List.xls.

 

[Patrick Simonds]

Disregard I posted before your update.

The problem with that is that Vaction.xls is the active workbook and this
code is performing a sort on Employee List.xls.

 

[Patrick Simonds]

Thank you that did the trick.

Lets try again -- I hit send too soon.

Sub PT_Driver_Last_Name_Sort()
'
' Macro1 Macro
' Macro recorded 12/20/2005 by Cathy Baker
'
'Sort by Paratransit Drivers Last Name

Dim bk as Workbook
set bk = Workbooks("Employee List.xls").
bk.Worksheets("Employee_List").Range("AA1").Value = 2

Dim wks As Worksheet
Set wks = bk.Worksheets("Employee_List")
With wks.Range("A1:Z300")
.Sort Key1:=wks.Range("E2"), Order1:=xlAscending, _
Key2:=wks.Range("F2"),Order2:=xlAscending, _
Key3:=wks.Range("A2"), Order3:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
End With

End Sub