ExcelBanter - Calling another workbook

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Calling another workbook (https://www.excelbanter.com/excel-programming/357879-calling-another-workbook.html)

Patrick Simonds

Calling another workbook

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

Tom Ogilvy

Calling another workbook

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

--
Regards,
Tom Ogilvy

"Patrick Simonds" wrote:

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

Tom Ogilvy

Calling another workbook

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

--
Regards,
Tom Ogilvy

"Patrick Simonds" wrote:

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

Patrick Simonds

Calling another workbook

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

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Patrick Simonds" wrote:

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

Patrick Simonds

Calling another workbook

Disregard I posted before your update.

"Patrick Simonds" wrote in message
...
The problem with that is that Vaction.xls is the active workbook and this
code is performing a sort on Employee List.xls.

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Patrick Simonds" wrote:

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

Patrick Simonds

Calling another workbook

Thank you that did the trick.

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Patrick Simonds" wrote:

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

All times are GMT +1. The time now is 12:18 PM.