copy and save excel worksheet without formulas

[primep]

I want to copy and save a worksheet "Template", below VBA is working good, but it is copying also the formulas, I want only cell values of above work to be copied and saved.

Thanks

Private Sub CommandButton1_Click() ' Save Excel Copy

Dim FileName As String

Dim FilePath As String

Dim NewBook As Workbook

FilePath = "D:\"

FileName = Sheets("Template").Range("g14") & ".xls"

If Dir(FilePath & "\" & FileName) <> "" Then

MsgBox "File " & FilePath & "\" & FileName & " already exists", vbInformation

Exit Sub

Else

Set NewBook = Workbooks.Add

ThisWorkbook.Sheets("Template").Copy before:=NewBook.Sheets(1)

Application.DisplayAlerts = False

NewBook.SaveAs FileName:=FilePath & FileName

NewBook.Activate

On Error Resume Next

ActiveSheet.OLEObjects.Visible = True

ActiveSheet.OLEObjects.Delete

On Error GoTo 0

NewBook.Save

NewBook.Close

End If

End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

Set NewBook = Workbooks.Add

ThisWorkbook.Sheets("Template").Copy before:=NewBook.Sheets(1)
With NewBook.Sheets(1).UsedRange
   .Value = .Value
End With
Application.DisplayAlerts = False

 

[primep]

Hi Fluff...
Thanks for your prompt guidance,
I added the code as per your reply, but VBA code is working same as before, its copying also the formulas,
please see the attached image

 

[Fluff]

Can you please post your code, when doing so please use code tags.

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

 

[primep]

ok below is the original code, please edit as required...

Private Sub CommandButton1_Click() ' Save Excel Copy
    Dim FileName As String
    Dim FilePath As String
    Dim NewBook As Workbook
    Dim ws As Worksheet
    FilePath = "D:\"
    FileName = Sheets("Template").Range("g14") & ".xls"
    If Dir(FilePath & "\" & FileName) <> "" Then
        MsgBox "File " & FilePath & "\" & FileName & " already exists", vbInformation
        Exit Sub
    Else
    Set NewBook = Workbooks.Add
    ws.UsedRange.Value = ws.UsedRange.Value
    ThisWorkbook.Sheets("Template").Copy before:=NewBook.Sheets(1)
    With NewBook.Sheets(1).UsedRange
   .Value = .Value
    End With
    Application.DisplayAlerts = False
    NewBook.SaveAs FileName:=FilePath & FileName
    NewBook.Activate
    On Error Resume Next
    ActiveSheet.OLEObjects.Visible = True
    ActiveSheet.OLEObjects.Delete
    On Error GoTo 0
    NewBook.Save
    NewBook.Close
    End If
End Sub

 

[Fluff]

I would expect this line

    ws.UsedRange.Value = ws.UsedRange.Value

to give you an error as you have not assigned anything to ws

 

[primep]

Hi Fluff...
But the original code is working fine without any error,
could you please suggest a fresh new code , as per the original code results (with only cell values to be saved in a new workbook, without showing formulas)

 

[Fluff]

The code I suggested will do exactly what you asked for. If the code you posted is your exact code, then I see no way that it can work.

 

[primep]

hi fluff,
sorry you were right, please see below original code...

Private Sub CommandButton1_Click() ' Save Excel Copy
    Dim FileName As String
    Dim FilePath As String
    Dim NewBook As Workbook
    FilePath = "D:\"
    FileName = Sheets("Template").Range("g14") & ".xls"
    If Dir(FilePath & "\" & FileName) <> "" Then
        MsgBox "File " & FilePath & "\" & FileName & " already exists", vbInformation
        Exit Sub
    Else
    Set NewBook = Workbooks.Add
    ThisWorkbook.Sheets("Template").Copy before:=NewBook.Sheets(1)
    With NewBook.Sheets(1).UsedRange
   .Value = .Value
    End With
    Application.DisplayAlerts = False
    NewBook.SaveAs FileName:=FilePath & FileName
    NewBook.Activate
    On Error Resume Next
    ActiveSheet.OLEObjects.Visible = True
    ActiveSheet.OLEObjects.Delete
    On Error GoTo 0
    NewBook.Save
    NewBook.Close
    End If
End Sub

 

[Fluff]

That should work & convert the formulae to values.
Step through the code using F8 until you get to this line

    ThisWorkbook.Sheets("Template").Copy before:=NewBook.Sheets(1)

Then in the VB editor have a look at the project for the new workbook, how many sheets does it contain?