Referring to a worsheet in another workbook

[Roundy]

I am attempting to get this code to work. The issue I am having is that the
worksheet named Socal Work Order is in another workbook. HOw do I refer to
it? Is there a way I can do it without using the workbook name, provided
both workbooks are open? I am using Office 2003 Pro SP2.

Dim Inrng As Range
Dim ORng As Range
Dim inWs As Worksheet
Dim outWs As Worksheet
Dim iLastRow As Long

Set inWs = ActiveSheet
Set outWs = Worksheets("SoCal Work Order")
Set ORng = outWs.Cells(outWs.Cells(92, 1).End(xlUp).Row + 1, "A")

TIA

 

[Jim Cone]

Sub GetItHereForFree()
On Error GoTo Err_Handler
Dim Inrng As Range
Dim ORng As Range
Dim inWs As Worksheet
Dim outWs As Worksheet
Dim WB As Excel.Workbook

On Error Resume Next
For Each WB In Excel.Workbooks
Set outWs = WB.Worksheets("SoCal Work Order")
If Err.Number = 0 Then Exit For
Err.Clear
Next
On Error GoTo Err_Handler

If Not outWs Is Nothing Then
Set ORng = outWs.Cells(92, 1).End(xlUp)(2, 1)
MsgBox ORng.Address(external:=True)
Else
MsgBox "Oops"
Exit Sub
End If

Set inWs = ActiveSheet
'something else
Exit Sub

Err_Handler:
'handle error
End Sub
------------

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Roundy"
<[email protected]>
wrote in message
I am attempting to get this code to work. The issue I am having is that the
worksheet named Socal Work Order is in another workbook. HOw do I refer to
it? Is there a way I can do it without using the workbook name, provided
both workbooks are open? I am using Office 2003 Pro SP2.

Dim Inrng As Range
Dim ORng As Range
Dim inWs As Worksheet
Dim outWs As Worksheet
Dim iLastRow As Long

Set inWs = ActiveSheet
Set outWs = Worksheets("SoCal Work Order")
Set ORng = outWs.Cells(outWs.Cells(92, 1).End(xlUp).Row + 1, "A")

TIA