Macro help
 

Hello everybody

Sub CopyToWorkbook()
Dim mypath As String

If [cmpname] = 0 Then
MsgBox ("company name not entered : enter company name")
Exit Sub
End If
Worksheets("form").Select
Worksheets("form").Copy.Values
On Error Resume Next
mypath = "e:\" & [cmpname] & "\"
MkDir mypath
ActiveWorkbook.SaveAs Filename:=mypath & [emp] & ".xls"
ActiveWorkbook.Close
Application.Goto Reference:="ename"
Worksheets("form").PrintPreview
End Sub

The macro copies the "form" sheet to a new workbook and saves it in the
folder and with assigned name. It works well.

The macro copies the sheet keeping the formulae alive. I want to copy the
sheet without formulae. i.e. only values.
How to modify it?
Any suggestions
Regards
Sreedhar

Macro help
 

Sreedhar

You could add something like the below into your code. This is not very
efficient as it just copies and paste special+Values ALL the cells on the
activesheet and then copies it to a new book. You could improve this by
testing for the dimensions of the data and just copying that for example.

Sub CopySheet()
With ActiveSheet
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues
.Copy
End With
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
web: www.excelusergroup.org
web: www.nickhodge.co.uk





"yshridhar" wrote in message
...
Hello everybody

Sub CopyToWorkbook()
Dim mypath As String

If [cmpname] = 0 Then
MsgBox ("company name not entered : enter company name")
Exit Sub
End If
Worksheets("form").Select
Worksheets("form").Copy.Values
On Error Resume Next
mypath = "e:\" & [cmpname] & "\"
MkDir mypath
ActiveWorkbook.SaveAs Filename:=mypath & [emp] & ".xls"
ActiveWorkbook.Close
Application.Goto Reference:="ename"
Worksheets("form").PrintPreview
End Sub

The macro copies the "form" sheet to a new workbook and saves it in the
folder and with assigned name. It works well.

The macro copies the sheet keeping the formulae alive. I want to copy the
sheet without formulae. i.e. only values.
How to modify it?
Any suggestions
Regards
Sreedhar

Macro help
 

Thanks Nick
With your suggestion i could able to solve the problem.
I add the following to macro
ActiveWorkbook.BreakLink Name:=fpath, Type:=xlExcelLinks

where fpath = filepathand name
Thanks alot
With regards
Sreedhar
"Nick Hodge" wrote:

Sreedhar

You could add something like the below into your code. This is not very
efficient as it just copies and paste special+Values ALL the cells on the
activesheet and then copies it to a new book. You could improve this by
testing for the dimensions of the data and just copying that for example.

Sub CopySheet()
With ActiveSheet
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues
.Copy
End With
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
web: www.excelusergroup.org
web: www.nickhodge.co.uk





"yshridhar" wrote in message
...
Hello everybody

Sub CopyToWorkbook()
Dim mypath As String

If [cmpname] = 0 Then
MsgBox ("company name not entered : enter company name")
Exit Sub
End If
Worksheets("form").Select
Worksheets("form").Copy.Values
On Error Resume Next
mypath = "e:\" & [cmpname] & "\"
MkDir mypath
ActiveWorkbook.SaveAs Filename:=mypath & [emp] & ".xls"
ActiveWorkbook.Close
Application.Goto Reference:="ename"
Worksheets("form").PrintPreview
End Sub

The macro copies the "form" sheet to a new workbook and saves it in the
folder and with assigned name. It works well.

The macro copies the sheet keeping the formulae alive. I want to copy the
sheet without formulae. i.e. only values.
How to modify it?
Any suggestions
Regards
Sreedhar