Dileep:

Try this one, it assumes that the workbook with sheet1 to be copied is open,
otherwise you have to write a bit of code to open it.

Sub SaveWorkbookASheet1()

'assume file A is open and the sheet is active

On Error GoTo error_line
Dim WSHShell As Object

Sheets("Sheet1").Copy

ActiveSheet.Cells.Copy
ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False
ActiveSheet.Cells(1, 1).Select

Set WSHShell = CreateObject("Wscript.Shell")
ChDir WSHShell.SpecialFolders("Desktop")

ActiveSheet.SaveAs Filename:="B.xls"
ActiveWorkbook.Close savechanges:=False

Set WSHShell = Nothing

Exit Sub

error_line:

Set WSHShell = Nothing

MsgBox "Error saving file, check", vbOKOnly
End Sub

--
Hope this helps
Martin Fishlock


"Dileep Chandran" wrote:

Hi,

Does anybody knows a macro to generate an excel file "B" from an excel
file "A" by pasting all data in file "A" as values and save in desktop?
(only sheet1)

Thanks in advance
-Dileep