object required error message
 

Hi all.

Receiving Run Time Error 424 "Object Required"
Whenever I try to run my code
Apolgies in advance for wrapping from copy/paste

Debugger comes back to he
Workbooks("my_Labor.xls").Sheets("Budget_Dat").Ran ge(A1.BL150).Copy _
Workbooks("my_Labor_Data.xls").Sheets("Budget_Dat" ).Range("A1")

Full code:
========================================
Sub Xtract()
Dim iSheets As Long

Application.DisplayAlerts = False

Workbooks.Add
ChDir "C:\WINDOWS\Temp"
With ActiveWorkbook
.SaveAs Filename:="C:\WINDOWS\Temp\my_Labor_Data.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:=""
_
ReadOnlyRecommended:=False, CreateBackup:=False
End With

If Worksheets.Count < 3 Then
For iSheets = Worksheets.Count + 1 To 3
Sheets.Add
Next iSheets
End If

'Rename sheets to match source file
Sheets("Sheet1").Name = "schedule_dat"
Sheets("Sheet2").Name = "actual_dat"
Sheets("Sheet3").Name = "budget_dat"

'Copy to data file to be email'd
Call Copy_Data

'Save and close data file
ChDir "C:\WINDOWS\Temp"
Workbooks("my_Labor_Data.xls").Save
Workbooks("my_Labor_Data.xls").Close

Application.DisplayAlerts = True


End Sub

Sub Copy_Data()
'Copy to Dat
file============================================== ===
'Budget_Data
Workbooks("my_Labor.xls").Sheets("Budget_Dat").Ran ge(A1.BL150).Cop
_
Workbooks("my_Labor_Data.xls").Sheets("Budget_Dat" ).Range("A1")

'Schedule_Data

Workbooks("my_Labor.xls").Sheets("Schedule_Dat").R ange(A1.BL150).Cop
_
Workbooks("my_Labor_Data.xls").Sheets("Schedule_Da t").Range("A1")

'Atual_Data
Workbooks("my_Labor.xls").Sheets("Actual_Dat").Ran ge(A1.BL150).Cop
_
Workbooks("my_Labor_Data.xls").Sheets("Actual_Dat" ).Range("A1")

End Su

--
Message posted from http://www.ExcelForum.com

object required error message
 

Goss

does the Workbook "my_Labor.xls" exist and is it open already? If it's not
open, that's your problem.

Regards

Trevor


"goss " wrote in message
...
Hi all.

Receiving Run Time Error 424 "Object Required"
Whenever I try to run my code
Apolgies in advance for wrapping from copy/paste

Debugger comes back to he
Workbooks("my_Labor.xls").Sheets("Budget_Dat").Ran ge(A1.BL150).Copy _
Workbooks("my_Labor_Data.xls").Sheets("Budget_Dat" ).Range("A1")

Full code:
========================================
Sub Xtract()
Dim iSheets As Long

Application.DisplayAlerts = False

Workbooks.Add
ChDir "C:\WINDOWS\Temp"
With ActiveWorkbook
SaveAs Filename:="C:\WINDOWS\Temp\my_Labor_Data.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
_
ReadOnlyRecommended:=False, CreateBackup:=False
End With

If Worksheets.Count < 3 Then
For iSheets = Worksheets.Count + 1 To 3
Sheets.Add
Next iSheets
End If

'Rename sheets to match source file
Sheets("Sheet1").Name = "schedule_dat"
Sheets("Sheet2").Name = "actual_dat"
Sheets("Sheet3").Name = "budget_dat"

'Copy to data file to be email'd
Call Copy_Data

'Save and close data file
ChDir "C:\WINDOWS\Temp"
Workbooks("my_Labor_Data.xls").Save
Workbooks("my_Labor_Data.xls").Close

Application.DisplayAlerts = True


End Sub

Sub Copy_Data()
'Copy to Data
file============================================== ===
'Budget_Data
Workbooks("my_Labor.xls").Sheets("Budget_Dat").Ran ge(A1.BL150).Copy
_
Workbooks("my_Labor_Data.xls").Sheets("Budget_Dat" ).Range("A1")

'Schedule_Data

Workbooks("my_Labor.xls").Sheets("Schedule_Dat").R ange(A1.BL150).Copy
_
Workbooks("my_Labor_Data.xls").Sheets("Schedule_Da t").Range("A1")

'Atual_Data
Workbooks("my_Labor.xls").Sheets("Actual_Dat").Ran ge(A1.BL150).Copy
_
Workbooks("my_Labor_Data.xls").Sheets("Actual_Dat" ).Range("A1")

End Sub


---
Message posted from http://www.ExcelForum.com/

object required error message
 

Thanks Trevor
Yup the wb exists and open.
In fact the code is contained within my_labor.xls
Trying to copy to my_labor_data.xls
in c:\windows\temp\
file may or may not pre-exist depending on system being used to create
xtract file

application.displayalerts......
should handle any prompts


---
Message posted from http://www.ExcelForum.com/

object required error message
 

Hi
In Excel 97 I used to have a problem if the clipboard wasn't cleared
and I was trying to copy. Try putting
Application.CutCopyMode = False before the copy

regards
Paul

goss wrote in message ...
Thanks Trevor
Yup the wb exists and open.
In fact the code is contained within my_labor.xls
Trying to copy to my_labor_data.xls
in c:\windows\temp\
file may or may not pre-exist depending on system being used to create
xtract file

application.displayalerts......
should handle any prompts


---
Message posted from http://www.ExcelForum.com/

object required error message
 

Thanks Paul
I put the cutcopymode = false b4 the first line of Sub Copy_Data
Receive same error code same line as b4


---
Message posted from http://www.ExcelForum.com/

object required error message
 

The problem was in referring to the range

Range(A1.BL150)
Should be ("A1:BL150")

-goss


---
Message posted from http://www.ExcelForum.com/

object required error message
 

Alas, it is so easy to overlook the obvious. ;-)


"goss " wrote in message
...
The problem was in referring to the range

Range(A1.BL150)
Should be ("A1:BL150")

-goss


---
Message posted from http://www.ExcelForum.com/