Forum Discussion

SandyKP_84's avatar
SandyKP_84
Copper Contributor
Mar 24, 2024

Saving spreadsheet without the formulas

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?

  • JoeUser2004's avatar
    JoeUser2004
    Bronze Contributor

    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.

  • 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

Resources