Macro help

[yshridhar]

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

 

[Nick Hodge]

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
(e-mail address removed)
web: www.excelusergroup.org
web: www.nickhodge.co.uk

 

[yshridhar]

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

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
(e-mail address removed)
web: www.excelusergroup.org
web: www.nickhodge.co.uk

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