Forgot the attenuation dot.

Sub Define_Old_List()

With Workbooks("Process_unit_directory.xls")
Set Old_List = .Sheets("Old_Unit_Roster").Range(ActiveCell, _
ActiveCell.End(xlDown))
End With
End Sub

Sub Define_New_List()

With Workbooks("Process_unit_directory.xls")
Set New_List = .Sheets("New_Unit_Roster").Range(ActiveCell, _
ActiveCell.End(xlDown))
End With
End Sub



"JLGWhiz" wrote in message
...
Try this apprach:

Option Explicit
Dim Old_List As Range
Dim New_List As Range
Sub Define_Old_List()

With Workbooks("Process_unit_directory.xls")
Set Old_List = Sheets("Old_Unit_Roster").Range(ActiveCell, _
ActiveCell.End(xlDown))
End With
End Sub

Sub Define_New_List()

With Workbooks("Process_unit_directory.xls")
Set New_List = Sheets("New_Unit_Roster").Range(ActiveCell, _
ActiveCell.End(xlDown))
End With
End Sub

You could actually do this in one sub.



"Bert Onstott" wrote in message
...

Can anyone tell me why the following code generates an error 1004 when
running either Sub? It's really weird - I can get it to run without an
error
if I start from scratch by activating only the workbook, the add the
worksheet, then add the range. But then the error comes back after I add
the
next Sub.

I reinstalled Excel, just in case. This looks as simple as it could be,
but
I'm obviously missing something. Wish I could attach the file.

The workbook contains three worksheets, two of which are named
Old_Unit_Roster and New_Unit_Roster.


Option Explicit
Dim Old_List As Range
Dim New_List As Range

Sub Define_Old_List()
Workbooks("Process_unit_directory.xls").Worksheets ("Old_Unit_Roster").Range("A1").Activate
Set Old_List = Sheets("Old_Unit_Roster").Range(ActiveCell,
ActiveCell.End(xlDown))

End Sub

Sub Define_New_List()
Workbooks("Process_unit_directory.xls").Worksheets ("New_Unit_Roster").Range("A1").Activate
Set New_List = Sheets("New_Unit_Roster").Range(ActiveCell,
ActiveCell.End(xlDown))

End Sub


Bert Onstott
3303 N. Sutton Sq
Stafford, TX
281-494-9644