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

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
|
|

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
|
|

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
|
|