Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run code without opening worksheet
How can I run this code without making worksheet "Outstanding Checks"
active? Sub Sort_Checks() ' ' Sort_Checks Macro ' ' Sheets("Outstanding Checks").Select Range("A5:D22").Select ActiveWorkbook.Worksheets("Outstanding Checks").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Outstanding Checks").Sort.SortFields.Add Key:= _ Range("A5:A22"), SortOn:=SortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("Outstanding Checks").Sort .SetRange Range("A5:D22") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("A4").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run code without opening worksheet
Save this code in a *.vbs file
Option Explicit Dim filePath, oExcel filePath = "c:\Test.xls" Set oExcel = CreateObject("Excel.Application") oExcel.Workbooks.Open(filepath) oExcel.Run "macro1" oExcel.ActiveWorkbook.Save oExcel.ActiveWorkbook.Close oExcel.Quit set oSheet = Nothing Set oExcel = Nothing -- Regards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect "Patrick Simonds" wrote: | How can I run this code without making worksheet "Outstanding Checks" | active? | | Sub Sort_Checks() | ' | ' Sort_Checks Macro | ' | | ' | Sheets("Outstanding Checks").Select | Range("A5:D22").Select | ActiveWorkbook.Worksheets("Outstanding Checks").Sort.SortFields.Clear | ActiveWorkbook.Worksheets("Outstanding Checks").Sort.SortFields.Add | Key:= _ | Range("A5:A22"), SortOn:=SortOnValues, Order:=xlAscending, | DataOption:= _ | xlSortNormal | With ActiveWorkbook.Worksheets("Outstanding Checks").Sort | .SetRange Range("A5:D22") | .Header = xlGuess | .MatchCase = False | .Orientation = xlTopToBottom | .SortMethod = xlPinYin | .Apply | End With | Range("A4").Select | End Sub | | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run code without opening worksheet
Maybe I should ask this in another way.
The code below is run when I close (click on the Finish button) my UserForm. I want this code to run without making the Worksheets("Outstanding Checks") active. The UserForm is called from any of a number of worksheets in the workbook, and places data onto a number of worksheets. But when I click the Finish button and run the code (and close the UserForm) I want to be in the same worksheet from which I called the UserForm. Sub Sort_Checks() ' ' Sort_Checks Macro ' ' Sheets("Outstanding Checks").Select Range("A5:D22").Select ActiveWorkbook.Worksheets("Outstanding Checks").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Outstanding Checks").Sort.SortFields.Add Key:= _ Range("A5:A22"), SortOn:=SortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("Outstanding Checks").Sort .SetRange Range("A5:D22") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("A4").Select End Sub "Dave Patrick" wrote in message ... Save this code in a *.vbs file Option Explicit Dim filePath, oExcel filePath = "c:\Test.xls" Set oExcel = CreateObject("Excel.Application") oExcel.Workbooks.Open(filepath) oExcel.Run "macro1" oExcel.ActiveWorkbook.Save oExcel.ActiveWorkbook.Close oExcel.Quit set oSheet = Nothing Set oExcel = Nothing -- Regards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect "Patrick Simonds" wrote: | How can I run this code without making worksheet "Outstanding Checks" | active? | | Sub Sort_Checks() | ' | ' Sort_Checks Macro | ' | | ' | Sheets("Outstanding Checks").Select | Range("A5:D22").Select | ActiveWorkbook.Worksheets("Outstanding Checks").Sort.SortFields.Clear | ActiveWorkbook.Worksheets("Outstanding Checks").Sort.SortFields.Add | Key:= _ | Range("A5:A22"), SortOn:=SortOnValues, Order:=xlAscending, | DataOption:= _ | xlSortNormal | With ActiveWorkbook.Worksheets("Outstanding Checks").Sort | .SetRange Range("A5:D22") | .Header = xlGuess | .MatchCase = False | .Orientation = xlTopToBottom | .SortMethod = xlPinYin | .Apply | End With | Range("A4").Select | End Sub | | |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run code without opening worksheet
Hi Patrick.
Try removing the selectioms ftom your code. Selections are rarely necessary and are usually inefficient. I have not looked at your code other than to remove the selections, but try something like: '============= Sub Sort_Checks() Dim WB As workkbook Dim SH As Worksheet Set WB = ThisWorkbook Set SH = WB.Sheets("Outstanding Checks") With SH .SortFields.Clear .Sort.SortFields.Add Key:=.Range("A5:A22"), _ SortOn:=SortOnValues, _ Order:=xlAscending, _ DataOption:=xlSortNormal With .Sort .SetRange SH.Range("A5:D22") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End With End Sub '<<============= -- --- Regards, Norman "Patrick Simonds" wrote in message ... Maybe I should ask this in another way. The code below is run when I close (click on the Finish button) my UserForm. I want this code to run without making the Worksheets("Outstanding Checks") active. The UserForm is called from any of a number of worksheets in the workbook, and places data onto a number of worksheets. But when I click the Finish button and run the code (and close the UserForm) I want to be in the same worksheet from which I called the UserForm. Sub Sort_Checks() ' ' Sort_Checks Macro ' ' Sheets("Outstanding Checks").Select Range("A5:D22").Select ActiveWorkbook.Worksheets("Outstanding Checks").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Outstanding Checks").Sort.SortFields.Add Key:= _ Range("A5:A22"), SortOn:=SortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("Outstanding Checks").Sort .SetRange Range("A5:D22") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("A4").Select End Sub "Dave Patrick" wrote in message ... Save this code in a *.vbs file Option Explicit Dim filePath, oExcel filePath = "c:\Test.xls" Set oExcel = CreateObject("Excel.Application") oExcel.Workbooks.Open(filepath) oExcel.Run "macro1" oExcel.ActiveWorkbook.Save oExcel.ActiveWorkbook.Close oExcel.Quit set oSheet = Nothing Set oExcel = Nothing -- Regards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect "Patrick Simonds" wrote: | How can I run this code without making worksheet "Outstanding Checks" | active? | | Sub Sort_Checks() | ' | ' Sort_Checks Macro | ' | | ' | Sheets("Outstanding Checks").Select | Range("A5:D22").Select | ActiveWorkbook.Worksheets("Outstanding Checks").Sort.SortFields.Clear | ActiveWorkbook.Worksheets("Outstanding Checks").Sort.SortFields.Add | Key:= _ | Range("A5:A22"), SortOn:=SortOnValues, Order:=xlAscending, | DataOption:= _ | xlSortNormal | With ActiveWorkbook.Worksheets("Outstanding Checks").Sort | .SetRange Range("A5:D22") | .Header = xlGuess | .MatchCase = False | .Orientation = xlTopToBottom | .SortMethod = xlPinYin | .Apply | End With | Range("A4").Select | End Sub | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
code does not work when opening again | Excel Programming | |||
run code on opening workbook and apply code to certain sheets | Excel Programming | |||
Opening a template .xls with VBA code | Excel Programming | |||
Opening Notepad from Code | Excel Programming | |||
Opening A Folder Using VB Code | Excel Programming |