VBA to save as not saving values.

[MattehWoo]

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

[Kenneth Hobs]

'ws.Cells.Copy
' ws.[A1].PasteSpecial Paste:=xlValues
[/COLOR]ws.cells.value = ws.cells.value

[MattehWoo]

Hmm trying this and i'm getting an out of memory error?

[Kenneth Hobs]

Try:

ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value

[MattehWoo]

It now does part of the job :)

But it's still showing an error "#NAME?" where the formula brought back the numbers.

[Kenneth Hobs]

It is hard to guess what is going on. Can you make a simple file and attach it? Attach by clicking the Go Advanced button and then the paperclip icon.

[MattehWoo]

17264

[Kenneth Hobs]

Since the UDF MattUsageCode() does not exist, you get the ?Name$ error.

[MattehWoo]

Hmm. If i made the code copy over the formula with values before doing this part, it should work?

[Kenneth Hobs]

Yes, that was what I was showing you how to do. Was that not the point of post #1?

[MattehWoo]

I obviously confused myself. But it's all working now!

Many thanks for your help Mr Hobs!