Error 1004

[Bert Onstott]

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

 

[JLGWhiz]

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.

 

[Jacob Skaria]

Try

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

If this post helps click Yes

 

[JLGWhiz]

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

 

[Jacob Skaria]

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

 

[Bert Onstott]

Sorry, but that gives the same error.

 

[Jacob Skaria]

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

 

[Bert Onstott]

Sorry, but that still gives an error 1004, "Application or object defined
error".

 

[Bert Onstott]

Sorry, but I still get an error 1004.

 

[Bert Onstott]

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]

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

 

[Patrick Molloy]

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

 

[Per Jessen]

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

 

[john]

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

 

[Bert Onstott]

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

 

[JLGWhiz]

Check your spelling real close. The syntax is OK.

 

[Patrick Molloy]

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

 

[Patrick Molloy]

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