Error 1004

B

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
 
J

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.
 
J

Jacob Skaria

Try

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

If this post helps click Yes
 
J

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
 
J

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
 
J

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
 
B

Bert Onstott

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

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.
 
B

Bert Onstott

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

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
 
P

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
 
J

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
 
B

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
 
P

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
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top