I'm having problems using pastespecial. These are the steps the code
performs:

1) Open workbook A
2) Select specific worksheet
3) Copy the data from a specifig range
4) Creates/Opens a new blank workbook
5) Pastes the values of the cells in the range into the first worksheet
(Sheet1) of the new workbook starting at A1
6) Saves the new workbook as a tab delimite file
7) closs and exits

Everything works except step 5. If I use paste, it works except it doesn't
give me the value in cells.



Set Xl = CreateObject("Excel.Application")
Xl.Visible = False
Xl.Workbooks.Open("C:\source.xls")
Xl.Worksheets("MySheet").Select
Xl.Range("B2:C12").Select
Xl.Selection.Copy
Xl.Workbooks.Add
Xl.Range("A1").Select
'Xl.ActiveSheet.PasteSpecial -4163,-4142,0,0
Xl.Selection.PasteSpecial -4163,-4142,0,0
Xl.Application.CutCopyMode = False
Xl.ActiveWorkbook.SaveAs "C:\test.txt",-4158,0
Xl.ActiveWorkbook.Close(0)
Xl.quit

The code above gives me :
"Microsoft Excel: PasteSpecial method of Range class failed"


If I try Xl.ActiveSheet.PasteSpecial -4163,-4142,0,0 instead, I get the
following:
"Microsoft Excel: PasteSpecial method of Worksheet class failed"


I need to use pastespecial because I want just the values of the cell.


Any help would be greatly appreciated.

There are two forms of pastespecial. Sheet and range. You are using the
Sheet form with the Range form arguments.

Xl.Range("A1").Select
Xl.ActiveSheet.Range("A1").PasteSpecial -4163,-4142,0,0

Should work.

--
Regards,
Tom Ogilvy

news.easynews.com wrote in message
...
I'm having problems using pastespecial. These are the steps the code
performs:

1) Open workbook A
2) Select specific worksheet
3) Copy the data from a specifig range
4) Creates/Opens a new blank workbook
5) Pastes the values of the cells in the range into the first worksheet
(Sheet1) of the new workbook starting at A1
6) Saves the new workbook as a tab delimite file
7) closs and exits

Everything works except step 5. If I use paste, it works except it doesn't
give me the value in cells.



Set Xl = CreateObject("Excel.Application")
Xl.Visible = False
Xl.Workbooks.Open("C:\source.xls")
Xl.Worksheets("MySheet").Select
Xl.Range("B2:C12").Select
Xl.Selection.Copy
Xl.Workbooks.Add
Xl.Range("A1").Select
'Xl.ActiveSheet.PasteSpecial -4163,-4142,0,0
Xl.Selection.PasteSpecial -4163,-4142,0,0
Xl.Application.CutCopyMode = False
Xl.ActiveWorkbook.SaveAs "C:\test.txt",-4158,0
Xl.ActiveWorkbook.Close(0)
Xl.quit

The code above gives me :
"Microsoft Excel: PasteSpecial method of Range class failed"


If I try Xl.ActiveSheet.PasteSpecial -4163,-4142,0,0 instead, I get the
following:
"Microsoft Excel: PasteSpecial method of Worksheet class failed"


I need to use pastespecial because I want just the values of the cell.


Any help would be greatly appreciated.