G
goss
Hi all.
Using xl xp pro
I'm receiving error code 1004
Unable to get pastespecial property fo range class:
Workbooks("my_Labor.xls").Sheets("Budget_Dat").Range("A1:BL150").Cop
_
Workbooks("my_Labor_Data.xls").Sheets("Budget_Dat") _
.Range("A1").PasteSpecial([xlpastetype = xlpastevalues])
Not sure where the problem is
Here's the 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
Application.CutCopyMode = True
End Sub
Sub Copy_Data()
'Copy to Dat
file=================================================
Application.CutCopyMode = False 'before the copy
'Budget_Data
Workbooks("my_Labor.xls").Sheets("Budget_Dat").Range("A1:BL150").Cop
_
Workbooks("my_Labor_Data.xls").Sheets("Budget_Dat") _
.Range("A1").PasteSpecial([xlpastetype = xlpastevalues])
'Schedule_Data
Workbooks("my_Labor.xls").Sheets("Schedule_Dat").Range("A1:BL150").Cop
_
Workbooks("my_Labor_Data.xls").Sheets("Schedule_Dat") _
.Range("A1").PasteSpecial([xlpastetype = xlpastevalues])
'Actual_Data
Workbooks("my_Labor.xls").Sheets("Actual_Dat").Range("A1:BL150").Cop
_
Workbooks("my_Labor_Data.xls").Sheets("Actual_Dat") _
.Range("A1").PasteSpecial([xlpastetype = xlpastevalues])
End Su
Using xl xp pro
I'm receiving error code 1004
Unable to get pastespecial property fo range class:
Workbooks("my_Labor.xls").Sheets("Budget_Dat").Range("A1:BL150").Cop
_
Workbooks("my_Labor_Data.xls").Sheets("Budget_Dat") _
.Range("A1").PasteSpecial([xlpastetype = xlpastevalues])
Not sure where the problem is
Here's the 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
Application.CutCopyMode = True
End Sub
Sub Copy_Data()
'Copy to Dat
file=================================================
Application.CutCopyMode = False 'before the copy
'Budget_Data
Workbooks("my_Labor.xls").Sheets("Budget_Dat").Range("A1:BL150").Cop
_
Workbooks("my_Labor_Data.xls").Sheets("Budget_Dat") _
.Range("A1").PasteSpecial([xlpastetype = xlpastevalues])
'Schedule_Data
Workbooks("my_Labor.xls").Sheets("Schedule_Dat").Range("A1:BL150").Cop
_
Workbooks("my_Labor_Data.xls").Sheets("Schedule_Dat") _
.Range("A1").PasteSpecial([xlpastetype = xlpastevalues])
'Actual_Data
Workbooks("my_Labor.xls").Sheets("Actual_Dat").Range("A1:BL150").Cop
_
Workbooks("my_Labor_Data.xls").Sheets("Actual_Dat") _
.Range("A1").PasteSpecial([xlpastetype = xlpastevalues])
End Su