How to change to not active workbook
I recorded the code below to sort my list of employees, and on it's own it
works great. My problem is that while I have WorkBook open it is not the Active WorkBook. I am calling the sort routine from within another WorkBook. Private Sub CommandButton1_Click() 'Sort by Fixed Route Driver Names Application.Run "EmployeeList.xls!FRDSort" End Sub ================================================== ===== Sub FRDSort() 'Sort by Fixed Route Drivers Names Module1.Disable_Events Module1.UnprotectWorkSheet Range("B1").Value = "3" ActiveWorkbook.Worksheets("Employee_List").Sort.So rtFields.Clear ActiveWorkbook.Worksheets("Employee_List").Sort.So rtFields.Add Key:=Range( _ "F2:F300"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("Employee_List").Sort.So rtFields.Add Key:=Range( _ "D2:D300"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("Employee_List").Sort .SetRange Range("A2:I300") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("A1").Select Module1.Enable_Events Module1.ProtectWorkSheet End Sub |
How to change to not active workbook
Change ActiveWorkbook with a reference to the workbook where you want to do
the work Workbooks("MyOtherBook.xls") instead of ActiveWorkbook. I can't say for the Module.Unprotectworksheet code or the Disable_Events code. But if it is set up to work on the activeworkbook, you would need changes there also. -- Regards, Tom Ogilvy "Patrick Simonds" wrote in message ... I recorded the code below to sort my list of employees, and on it's own it works great. My problem is that while I have WorkBook open it is not the Active WorkBook. I am calling the sort routine from within another WorkBook. Private Sub CommandButton1_Click() 'Sort by Fixed Route Driver Names Application.Run "EmployeeList.xls!FRDSort" End Sub ================================================== ===== Sub FRDSort() 'Sort by Fixed Route Drivers Names Module1.Disable_Events Module1.UnprotectWorkSheet Range("B1").Value = "3" ActiveWorkbook.Worksheets("Employee_List").Sort.So rtFields.Clear ActiveWorkbook.Worksheets("Employee_List").Sort.So rtFields.Add Key:=Range( _ "F2:F300"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("Employee_List").Sort.So rtFields.Add Key:=Range( _ "D2:D300"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("Employee_List").Sort .SetRange Range("A2:I300") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("A1").Select Module1.Enable_Events Module1.ProtectWorkSheet End Sub |
All times are GMT +1. The time now is 09:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com