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
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