Excel after save as get #REF for formulas

Question

I need a macro that creates a duplicate workbook with all sheets (except for "Data List") and all macros/userforms. This is what I have so far:

fname = Application.GetSaveAsFilename(FileFilter:="Excel Files (*.xlsm), *.xlsm")
ActiveWorkbook.SaveAs Filename:=fname, FileFormat:=52

Application.DisplayAlerts = False
Worksheets("Data List").Delete

The problem is, my formulas stop working since they refer back to "Data List". How can I solve this?

Context/Edit:

Sorry, my question was poorly worded. I forgot to include the fact that I was getting #REF errors because some formulas referred back to the original workbook, which has the "Data List" sheet. Previously, I copied all the sheets EXCEPT the "Data List" into a new workbook, and everything worked like a charm until I sent the file over to another person, and then all the macro's failed. So I'm in the process of finding a work around to this problem, hence saving the entire workbook and then deleting one worksheet. However, now my formulas are not working.

Sorry, my question was poorly worded. I forgot to include the fact that I was getting #REF errors because some formulas referred back to the original workbook, which has the "Data List" sheet. Previously, I copied all the sheets EXCEPT the "Data List" into a new workbook, and everything worked like a charm until I sent the file over to another person, and then all the macro's failed. So I'm in the process of finding a work around to this problem, hence saving the entire workbook and then deleting one worksheet. However, now my formulas are not working. — jwoff
for w = 1 to worksheets.count: worksheets(w).cells = worksheets(w).cells .value: next w: Worksheets("Data List").Delete — user4039065
How do I implement this? I replaced Worksheets("Data List").Delete with your code and I get runtime error 7, "Out of memory" on the "For w = 1 To Worksheets.Count: Worksheets(w).Cells = Worksheets(w).Cells.Value" line. Could you post the full code as an answer so I can mark it as solved? Thank you! — jwoff

Unknown Unknown · Accepted Answer · 2015-08-27T08:37:53

Here is a basic framework. You should be able to add bells and whistles if you like.

Sub flatten_WB()
    Dim fn As String, w As Long

    fn = Application.GetSaveAsFilename(FileFilter:="Excel Files (*.xlsm), *.xlsm")

    On Error Resume Next   'don't know if one worksheet may have zero formulas
    Application.DisplayAlerts = False  'might need this for the SaveAs as well as delete worksheet

    With ActiveWorkbook
        '.Save   'I would save the original before saving as a new workbook
        .SaveAs Filename:=fn, FileFormat:=52

        For w = 1 To .Worksheets.Count
            With .Worksheets(w)
                If .Name <> "Data List" Then 'don't bother doing the one we are nuking
                    .Cells.SpecialCells(xlCellTypeFormulas) = _
                       .Cells.SpecialCells(xlCellTypeFormulas).Value
                End If
            End With
        Next w

        .Worksheets("Data List").Delete
    End With
    Application.DisplayAlerts = True
    On Error GoTo 0
End Sub

Get a new filename.
SaveAs the new filename (as macro-enabled workbook). Might want to consider saving the original before the SaveAs.
Cycle through all worksheets (except the one to be deleted) and convert all formulas to their returned values.
Delete the 'Data List' worksheet.
Restore the application environment.

Excel after save as get #REF for formulas

1 Answers