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

Try this for the activesheet
It save the file on your desktop

Sub Copy_ActiveSheet()
Dim wb As Workbook
Dim strdate As String
Dim WshShell As Object
Dim SpecialPath As String

Set WshShell = CreateObject("WScript.Shell")
SpecialPath = WshShell.SpecialFolders("Desktop")

strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False

ActiveSheet.Copy
Set wb = ActiveWorkbook

With wb.Sheets(1).UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False

With wb
.SaveAs SpecialPath & "\Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
.Close False
End With
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Dileep Chandran" wrote in message ups.com...
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

Thanks Ron, It works awesome. Is it possible to copy the informations
to a specified sheet in a specified file stored in a network drive?

Thanks again for the timely help

-Dileep

Yes this is possible

Do you want to copy a range to the workbook stored in a network drive
in a hard coded destination cell ?

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Dileep Chandran" wrote in message oups.com...
Thanks Ron, It works awesome. Is it possible to copy the informations
to a specified sheet in a specified file stored in a network drive?

Thanks again for the timely help

-Dileep

Yes Ron. You got it right.

You can try this basic example

First it check if test.xls is open
If not open in open H:\test.xls

Then it copy the range from the workbook with the code (thisworkbook)
Worksheets("Sheet1").Range("A1:C10")

To A1 in "Sheet1" of H:\test.xls
destWB.Worksheets("Sheet1").Range("A1")

After that it save test.xls and close it


Sub copy_to_another_workbook()
Dim sourceRange As Range
Dim destrange As Range
Dim destWB As Workbook

Application.ScreenUpdating = False
If bIsBookOpen("test.xls") Then
Set destWB = Workbooks("test.xls")
Else
Set destWB = Workbooks.Open("H:\test.xls")
End If
Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("A1:C10")
Set destrange = destWB.Worksheets("Sheet1").Range("A1")
sourceRange.Copy
destrange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
destWB.Close True
Application.ScreenUpdating = True
End Sub


'Copy this function also in the module

Function bIsBookOpen(ByRef szBookName As String) As Boolean
' Rob Bovey
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function


--

Regards Ron de Bruin
http://www.rondebruin.nl



"Dileep Chandran" wrote in message
ups.com...
Yes Ron. You got it right.

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

Thank You, Martin......