paste special
 

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").Ran ge("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").Ran ge("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").R ange("A1:BL150").Cop
_
Workbooks("my_Labor_Data.xls").Sheets("Schedule_Da t") _
.Range("A1").PasteSpecial([xlpastetype = xlpastevalues])

'Actual_Data

Workbooks("my_Labor.xls").Sheets("Actual_Dat").Ran ge("A1:BL150").Cop
_
Workbooks("my_Labor_Data.xls").Sheets("Actual_Dat" ) _
.Range("A1").PasteSpecial([xlpastetype = xlpastevalues])


End Su

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

paste special
 

Hello Goss

I would not use paste special

Try something like this
set rng1 =Workbooks("my_Labor.xls").Sheets _
("Budget_Dat").Range("A1:BL150")

set rng2 =Workbooks("my_Labor_Data.xls").Sheets
("Budget_Dat") .Range("A1:BL150")

rng2.value = rng1.value

This copies the values and not formulas.

Regards
Peter
-----Original Message-----
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").Ra nge
("A1:BL150").Copy
_
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 Data
file============================================= ====

Application.CutCopyMode = False 'before the copy

'Budget_Data

Workbooks("my_Labor.xls").Sheets("Budget_Dat").Ra nge
("A1:BL150").Copy
_
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").Copy
_
Workbooks("my_Labor_Data.xls").Sheets("Schedule_D at") _
.Range("A1").PasteSpecial([xlpastetype = xlpastevalues])

'Actual_Data

Workbooks("my_Labor.xls").Sheets("Actual_Dat").Ra nge
("A1:BL150").Copy
_
Workbooks("my_Labor_Data.xls").Sheets("Actual_Dat ") _
.Range("A1").PasteSpecial([xlpastetype = xlpastevalues])


End Sub


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

.

paste special
 

Peter-
Worked perfectly!!


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