Error 1004
 

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

Error 1004
 

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

Error 1004
 

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

Error 1004
 

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

Error 1004
 

Bert, initially I only looked at the cause of the error. To reference a range
you dont need to activate the worksheet. Try the below code..Create a
worksheet object and refer the range....

Option Explicit
Dim Old_List As Range
Dim New_List As Range

Sub Define_Old_List()
Dim ws As Worksheet
Set ws = Workbooks("Process_unit_directory.xls").Worksheets ("Old_Unit_Roster")
Set Old_List = ws.Range(ActiveCell, ActiveCell.End(xlDown))
End Sub

Sub Define_New_List()
Dim ws As Worksheet
Set ws = Workbooks("Process_unit_directory.xls").Worksheets ("New_Unit_Roster")
Set New_List = ws.Range(ActiveCell, ActiveCell.End(xlDown))
End Sub




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

Error 1004
 

Corection...

Option Explicit
Dim Old_List As Range
Dim New_List As Range

Sub Define_Old_List()
Dim ws As Worksheet
Set ws = Workbooks("Process_unit_directory.xls").Worksheets ("Old_Unit_Roster")
Set Old_List = ws.Range("A1", ws.Range("A1").End(xlDown))
End Sub

Sub Define_New_List()
Dim ws As Worksheet
Set ws = Workbooks("Process_unit_directory.xls").Worksheets ("New_Unit_Roster")
Set Old_List = ws.Range("A1", ws.Range("A1").End(xlDown))
End Sub


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


"Jacob Skaria" wrote:

Bert, initially I only looked at the cause of the error. To reference a range
you dont need to activate the worksheet. Try the below code..Create a
worksheet object and refer the range....

Option Explicit
Dim Old_List As Range
Dim New_List As Range

Sub Define_Old_List()
Dim ws As Worksheet
Set ws = Workbooks("Process_unit_directory.xls").Worksheets ("Old_Unit_Roster")
Set Old_List = ws.Range(ActiveCell, ActiveCell.End(xlDown))
End Sub

Sub Define_New_List()
Dim ws As Worksheet
Set ws = Workbooks("Process_unit_directory.xls").Worksheets ("New_Unit_Roster")
Set New_List = ws.Range(ActiveCell, ActiveCell.End(xlDown))
End Sub




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

Error 1004
 

Sorry, but that gives the same error.
--
Bert Onstott
3303 N. Sutton Sq
Stafford, TX
281-494-9644


"JLGWhiz" wrote:

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

Error 1004
 

Sorry, but that still gives an error 1004, "Application or object defined
error".
--
Bert Onstott
3303 N. Sutton Sq
Stafford, TX
281-494-9644


"JLGWhiz" wrote:

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

Error 1004
 

Sorry, but I still get an error 1004.

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


"Jacob Skaria" wrote:

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

Error 1004
 

Sorry, this approach still gives an error 1004. Thanks for all of the
suggestions, but all of them give the same error as my original.

There must be something else wrong with either the workbook or my VBA.

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


"Jacob Skaria" wrote:

Bert, initially I only looked at the cause of the error. To reference a range
you dont need to activate the worksheet. Try the below code..Create a
worksheet object and refer the range....

Option Explicit
Dim Old_List As Range
Dim New_List As Range

Sub Define_Old_List()
Dim ws As Worksheet
Set ws = Workbooks("Process_unit_directory.xls").Worksheets ("Old_Unit_Roster")
Set Old_List = ws.Range(ActiveCell, ActiveCell.End(xlDown))
End Sub

Sub Define_New_List()
Dim ws As Worksheet
Set ws = Workbooks("Process_unit_directory.xls").Worksheets ("New_Unit_Roster")
Set New_List = ws.Range(ActiveCell, ActiveCell.End(xlDown))
End Sub




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

Error 1004
 

Well, that actually seems to work. Why that works and the other doesn't I
haven't a clue, but thanks anyway!

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


"Jacob Skaria" wrote:

Corection...

Option Explicit
Dim Old_List As Range
Dim New_List As Range

Sub Define_Old_List()
Dim ws As Worksheet
Set ws = Workbooks("Process_unit_directory.xls").Worksheets ("Old_Unit_Roster")
Set Old_List = ws.Range("A1", ws.Range("A1").End(xlDown))
End Sub

Sub Define_New_List()
Dim ws As Worksheet
Set ws = Workbooks("Process_unit_directory.xls").Worksheets ("New_Unit_Roster")
Set Old_List = ws.Range("A1", ws.Range("A1").End(xlDown))
End Sub


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


"Jacob Skaria" wrote:

Bert, initially I only looked at the cause of the error. To reference a range
you dont need to activate the worksheet. Try the below code..Create a
worksheet object and refer the range....

Option Explicit
Dim Old_List As Range
Dim New_List As Range

Sub Define_Old_List()
Dim ws As Worksheet
Set ws = Workbooks("Process_unit_directory.xls").Worksheets ("Old_Unit_Roster")
Set Old_List = ws.Range(ActiveCell, ActiveCell.End(xlDown))
End Sub

Sub Define_New_List()
Dim ws As Worksheet
Set ws = Workbooks("Process_unit_directory.xls").Worksheets ("New_Unit_Roster")
Set New_List = ws.Range(ActiveCell, ActiveCell.End(xlDown))
End Sub




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

Error 1004
 

at which line does the code break? switch on the macro recorder and select
the workbook and the sheet

is the name Process_unit_directory.xls correct?

error 1004 suggests that the object can't be assigned because it isn't
recognized

"Bert Onstott" wrote in message
...
Sorry, this approach still gives an error 1004. Thanks for all of the
suggestions, but all of them give the same error as my original.

There must be something else wrong with either the workbook or my VBA.

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


"Jacob Skaria" wrote:

Bert, initially I only looked at the cause of the error. To reference a
range
you dont need to activate the worksheet. Try the below code..Create a
worksheet object and refer the range....

Option Explicit
Dim Old_List As Range
Dim New_List As Range

Sub Define_Old_List()
Dim ws As Worksheet
Set ws =
Workbooks("Process_unit_directory.xls").Worksheets ("Old_Unit_Roster")
Set Old_List = ws.Range(ActiveCell, ActiveCell.End(xlDown))
End Sub

Sub Define_New_List()
Dim ws As Worksheet
Set ws =
Workbooks("Process_unit_directory.xls").Worksheets ("New_Unit_Roster")
Set New_List = ws.Range(ActiveCell, ActiveCell.End(xlDown))
End Sub




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

Error 1004
 

Hi

I think the problem is either the workbook name or the sheet names which
isn't correct.

Try this improved version of Jacob's last suggestion. Look at the
highlighted line when you hit debug, it will indicate which name(s) are
wrong.


Sub Define_Old_List1()
Dim wb As Workbook
Dim ws As Worksheet

Set wb = Workbooks("Process_unit_directory.xls")
Set ws = wb.Worksheets("Old_Unit_Roster")
Set Old_List = ws.Range("A1", ws.Range("A1").End(xlDown))
End Sub

Sub Define_New_List1()
Dim wb As Workbook
Dim ws As Worksheet

Set wb = Workbooks("Process_unit_directory.xls")
Set ws = wb.Worksheets("New_Unit_Roster")
Set Old_List = ws.Range("A1", ws.Range("A1").End(xlDown))
End Sub

Hopes this helps.
....
Per

"Bert Onstott" skrev i meddelelsen
...
Sorry, this approach still gives an error 1004. Thanks for all of the
suggestions, but all of them give the same error as my original.

There must be something else wrong with either the workbook or my VBA.

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


"Jacob Skaria" wrote:

Bert, initially I only looked at the cause of the error. To reference a
range
you dont need to activate the worksheet. Try the below code..Create a
worksheet object and refer the range....

Option Explicit
Dim Old_List As Range
Dim New_List As Range

Sub Define_Old_List()
Dim ws As Worksheet
Set ws =
Workbooks("Process_unit_directory.xls").Worksheets ("Old_Unit_Roster")
Set Old_List = ws.Range(ActiveCell, ActiveCell.End(xlDown))
End Sub

Sub Define_New_List()
Dim ws As Worksheet
Set ws =
Workbooks("Process_unit_directory.xls").Worksheets ("New_Unit_Roster")
Set New_List = ws.Range(ActiveCell, ActiveCell.End(xlDown))
End Sub




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

Error 1004
 

another way perhaps

Dim Old_List As Range
Dim New_List As Range

Sub Define_Old_List()
Dim wsOld As Worksheet

With Workbooks("Process_unit_directory.xls")

Set wsOld = .Worksheets("Old_Unit_Roster")

Set Old_List = wsOld.Range("A1", wsOld.Range("A1").End(xlDown))

End With

End Sub

Sub Define_New_List()
Dim wsNew As Worksheet

With Workbooks("Process_unit_directory.xls")

Set wsNew = .Worksheets("New_Unit_Roster")

Set Old_List = wsNew.Range("A1", wsNew.Range("A1").End(xlDown))

End With

End Sub
--
jb


"Bert Onstott" wrote:

Sorry, this approach still gives an error 1004. Thanks for all of the
suggestions, but all of them give the same error as my original.

There must be something else wrong with either the workbook or my VBA.

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


"Jacob Skaria" wrote:

Bert, initially I only looked at the cause of the error. To reference a range
you dont need to activate the worksheet. Try the below code..Create a
worksheet object and refer the range....

Option Explicit
Dim Old_List As Range
Dim New_List As Range

Sub Define_Old_List()
Dim ws As Worksheet
Set ws = Workbooks("Process_unit_directory.xls").Worksheets ("Old_Unit_Roster")
Set Old_List = ws.Range(ActiveCell, ActiveCell.End(xlDown))
End Sub

Sub Define_New_List()
Dim ws As Worksheet
Set ws = Workbooks("Process_unit_directory.xls").Worksheets ("New_Unit_Roster")
Set New_List = ws.Range(ActiveCell, ActiveCell.End(xlDown))
End Sub




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

Error 1004
 

Oddly enough, this version actually doesn't give an error.

Here's what I get using the macro recorder.

Sub Macro2()
'
' Macro2 Macro
'

'
Sheets("Old_Unit_Roster").Select
Range("A1").Select
Sheets("New_Unit_Roster").Select
Range("A1").Select
End Sub

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


"Per Jessen" wrote:

Hi

I think the problem is either the workbook name or the sheet names which
isn't correct.

Try this improved version of Jacob's last suggestion. Look at the
highlighted line when you hit debug, it will indicate which name(s) are
wrong.


Sub Define_Old_List1()
Dim wb As Workbook
Dim ws As Worksheet

Set wb = Workbooks("Process_unit_directory.xls")
Set ws = wb.Worksheets("Old_Unit_Roster")
Set Old_List = ws.Range("A1", ws.Range("A1").End(xlDown))
End Sub

Sub Define_New_List1()
Dim wb As Workbook
Dim ws As Worksheet

Set wb = Workbooks("Process_unit_directory.xls")
Set ws = wb.Worksheets("New_Unit_Roster")
Set Old_List = ws.Range("A1", ws.Range("A1").End(xlDown))
End Sub

Hopes this helps.
....
Per

"Bert Onstott" skrev i meddelelsen
...
Sorry, this approach still gives an error 1004. Thanks for all of the
suggestions, but all of them give the same error as my original.

There must be something else wrong with either the workbook or my VBA.

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


"Jacob Skaria" wrote:

Bert, initially I only looked at the cause of the error. To reference a
range
you dont need to activate the worksheet. Try the below code..Create a
worksheet object and refer the range....

Option Explicit
Dim Old_List As Range
Dim New_List As Range

Sub Define_Old_List()
Dim ws As Worksheet
Set ws =
Workbooks("Process_unit_directory.xls").Worksheets ("Old_Unit_Roster")
Set Old_List = ws.Range(ActiveCell, ActiveCell.End(xlDown))
End Sub

Sub Define_New_List()
Dim ws As Worksheet
Set ws =
Workbooks("Process_unit_directory.xls").Worksheets ("New_Unit_Roster")
Set New_List = ws.Range(ActiveCell, ActiveCell.End(xlDown))
End Sub




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

Error 1004
 

Check your spelling real close. The syntax is OK.

"Bert Onstott" wrote in message
...
Sorry, but that still gives an error 1004, "Application or object defined
error".
--
Bert Onstott
3303 N. Sutton Sq
Stafford, TX
281-494-9644


"JLGWhiz" wrote:

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








-----------------------------------------------------------------------------
Our Peering Chose change
Visit : http://spacesst.com/peerin

Error 1004
 

and put
OPTION EXPLICIT
at the top of the module. it enforces all variables to be dimensioned
properly - as is especially great for picking up typos


"JLGWhiz" wrote in message
...
Check your spelling real close. The syntax is OK.

"Bert Onstott" wrote in message
...
Sorry, but that still gives an error 1004, "Application or object defined
error".
--
Bert Onstott
3303 N. Sutton Sq
Stafford, TX
281-494-9644


"JLGWhiz" wrote:

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

Error 1004
 

so I'd guess that

Set wb = Workbooks("Process_unit_directory.xls")

is the issue? in the immediate window, type
?Workbooks(1).Name

so see if there's a typo

"Bert Onstott" wrote in message
...
Oddly enough, this version actually doesn't give an error.

Here's what I get using the macro recorder.

Sub Macro2()
'
' Macro2 Macro
'

'
Sheets("Old_Unit_Roster").Select
Range("A1").Select
Sheets("New_Unit_Roster").Select
Range("A1").Select
End Sub

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


"Per Jessen" wrote:

Hi

I think the problem is either the workbook name or the sheet names which
isn't correct.

Try this improved version of Jacob's last suggestion. Look at the
highlighted line when you hit debug, it will indicate which name(s) are
wrong.


Sub Define_Old_List1()
Dim wb As Workbook
Dim ws As Worksheet

Set wb = Workbooks("Process_unit_directory.xls")
Set ws = wb.Worksheets("Old_Unit_Roster")
Set Old_List = ws.Range("A1", ws.Range("A1").End(xlDown))
End Sub

Sub Define_New_List1()
Dim wb As Workbook
Dim ws As Worksheet

Set wb = Workbooks("Process_unit_directory.xls")
Set ws = wb.Worksheets("New_Unit_Roster")
Set Old_List = ws.Range("A1", ws.Range("A1").End(xlDown))
End Sub

Hopes this helps.
....
Per

"Bert Onstott" skrev i
meddelelsen
...
Sorry, this approach still gives an error 1004. Thanks for all of the
suggestions, but all of them give the same error as my original.

There must be something else wrong with either the workbook or my VBA.

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


"Jacob Skaria" wrote:

Bert, initially I only looked at the cause of the error. To reference
a
range
you dont need to activate the worksheet. Try the below code..Create a
worksheet object and refer the range....

Option Explicit
Dim Old_List As Range
Dim New_List As Range

Sub Define_Old_List()
Dim ws As Worksheet
Set ws =
Workbooks("Process_unit_directory.xls").Worksheets ("Old_Unit_Roster")
Set Old_List = ws.Range(ActiveCell, ActiveCell.End(xlDown))
End Sub

Sub Define_New_List()
Dim ws As Worksheet
Set ws =
Workbooks("Process_unit_directory.xls").Worksheets ("New_Unit_Roster")
Set New_List = ws.Range(ActiveCell, ActiveCell.End(xlDown))
End Sub




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