Saving spreadsheet without the formulas

Copper Contributor

I normally save my spreadsheet in a pdf format for client which eliminates the working formulas for markups - client now requires EXCEL format - how do I save the clients section just with the final amounts.  So need to send the 1st 5 columns without the rest but 4th and 5th columns are calculated by formulas using 8th, 9th and 10th columns - I can attach an example?

3 Replies

@SandyKP_84 

You could run this macro:

Sub SaveColumnsWithoutFormulas()
    Dim wss As Worksheet
    Dim wst As Worksheet
    Dim wbk As Workbook
    Application.ScreenUpdating = False
    Set wss = ActiveSheet
    Set wbk = Workbooks.Add(xlWBATWorksheet)
    Set wst = wbk.Worksheets(1)
    wss.Range("A:E").Copy
    With wst.Range("A1")
        .PasteSpecial Paste:=xlPasteValues
        .PasteSpecial Paste:=xlPasteFormats
    End With
    Application.ScreenUpdating = True
    Application.Dialogs(xlDialogSaveAs).Show
End Sub

@Hans Vogelaar thanks - appreciate it.

@SandyKP_84 

 

Select the cells, or simply press ctrl+a (sometimes twice -- sigh) to select all cells.

 

Then press f5 (Go To), click Special, select Formulas, and click OK.

 

Then press ctrl+c (copy), and with the cursor over the still-selected cells, right-click paste-values.