Error 1004

  • Thread starter Thread starter Bert Onstott
  • Start date Start date
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
 
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.
 
Try

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

If this post helps click Yes
 
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
 
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
 
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
 
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.
 
Well, that actually seems to work. Why that works and the other doesn't I
haven't a clue, but thanks anyway!
 
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
 
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
 
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
 
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
 
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
 
Back
Top