Try

Workbooks("Process_unit_directory.xls").Worksheets ("Old_Unit_Roster").Activate
Range("A1").Activate

If this post helps click Yes
---------------
Jacob Skaria


"Bert Onstott" wrote:


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