PDA

View Full Version : [SOLVED] Save sheet - New copy - Modify code witout formulas



emandero
10-24-2016, 11:57 PM
Hi

I have a code, and have used it for a long time. And works perfect. But we did some modifications of our
sheet, and got some new collums that have formulas.

I found the code from this page. But didnt find it again now. So it may have been solved before. I still want it
to make the same copy! But copy then "hard data", and not the formulas in the new sheet its saves.

Really appriciate any help i could get! Thanks.

The code:


Sub Create()
Dim NewName As String
Dim nm As Name
Dim ws As Worksheet
If MsgBox("New GF report?" _
, vbYesNo, "NewCopy") = vbNo Then
Exit Sub
With Application
.ScreenUpdating = False
' Copy specific sheets
' *SET THE SHEET NAMES TO COPY BELOW*
' Array("Sheet Name", "Another sheet name", "And Another"))
' Sheet names go inside quotes, seperated by commas
On Error GoTo ErrCatcher
Sheets(Array("Product")).Copy
On Error GoTo 0
' Input box to name new file
NewName = InputBox("Name of document?", "New Copy")
' Save it with the NewName and in the same directory as original
ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & NewName & ".xlsx"
ActiveWorkbook.Close SaveChanges:=False
.ScreenUpdating = True
End With
Exit Sub
ErrCatcher:
MsgBox "Error error!"
End Sub

mana
10-25-2016, 04:47 AM
Option Explicit

Sub test()
Dim NewName As String
Dim ws As Worksheet

NewName = InputBox("Name of document?", "New Copy")
If NewName = "" Then Exit Sub

Application.ScreenUpdating = False

ActiveWindow.SelectedSheets.Copy
For Each ws In Worksheets
ws.UsedRange.Value = ws.UsedRange.Value
Next

ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & NewName & ".xlsx"
ActiveWorkbook.Close SaveChanges:=False

End Sub