MattehWoo
09-30-2016, 02:25 AM
Hi all,
I have the following code which copies and saves selected worksheets into a new workbook. However it is not copying the values from the formulas and just brings them all over as '#N/A'.
Where am i going wrong?
Option Explicit
Sub Save()
Dim NewName As String
Dim nm As Name
Dim ws As Worksheet
If MsgBox("Save Data?" & vbCr & _
"This will close the tool." _
, vbYesNo, "NewCopy") = vbNo Then Exit Sub
With Application
.ScreenUpdating = False
On Error GoTo ErrCatcher
Sheets(Array("Numbers", "Graph")).Copy
On Error GoTo 0
For Each ws In ActiveWorkbook.Worksheets
ws.Cells.Copy
ws.[A1].PasteSpecial Paste:=xlValues
ws.Cells.Hyperlinks.Delete
Application.CutCopyMode = False
Cells(1, 1).Select
ws.Activate
Next ws
Cells(1, 1).Select
For Each nm In ActiveWorkbook.Names
nm.Delete
Next nm
Application.Dialogs(xlDialogSaveAs).Show
.ScreenUpdating = True
End With
Workbooks("Main Book").Close savechanges:=False
Exit Sub
End Sub
I have the following code which copies and saves selected worksheets into a new workbook. However it is not copying the values from the formulas and just brings them all over as '#N/A'.
Where am i going wrong?
Option Explicit
Sub Save()
Dim NewName As String
Dim nm As Name
Dim ws As Worksheet
If MsgBox("Save Data?" & vbCr & _
"This will close the tool." _
, vbYesNo, "NewCopy") = vbNo Then Exit Sub
With Application
.ScreenUpdating = False
On Error GoTo ErrCatcher
Sheets(Array("Numbers", "Graph")).Copy
On Error GoTo 0
For Each ws In ActiveWorkbook.Worksheets
ws.Cells.Copy
ws.[A1].PasteSpecial Paste:=xlValues
ws.Cells.Hyperlinks.Delete
Application.CutCopyMode = False
Cells(1, 1).Select
ws.Activate
Next ws
Cells(1, 1).Select
For Each nm In ActiveWorkbook.Names
nm.Delete
Next nm
Application.Dialogs(xlDialogSaveAs).Show
.ScreenUpdating = True
End With
Workbooks("Main Book").Close savechanges:=False
Exit Sub
End Sub