Saving a worksheet into a new workboook but using values instead of the referencing formulas

Question

So I am trying to create a workbook that uses multiple references from a worksheet of the initial data to auto fill cells in different worksheets to produce Forms (pre-formated worksheets). For one of the worksheets, I need to save it on a separate network drive as its own .xlsx workbook. So far, the code I have developed creates the new workbook, but the cells all still contain the original formulas that reference the original workbook. Is there a way, when saving into the new workbook, to convert the cells to values? Here is the sub I have in place. TIA

Private Sub SaveBidTab1_Click()
'   Saves the BidTab in the Current Year's Bid Tabs Folder in
'   Dave's Snapserver Construction Files

Dim BTFName As String   'this will be the name of the new file name saved in the Bid Tabs Folder
Dim BTFfolder As String 'This is the folder to save the form into
Dim BTFDate As String   'This is the date to choose which year's folder to use
Dim ProjectShortName As String  'This is the short name for the project for the file name
Dim NewBook As Workbook ' This is temp workbook that the new bid tab sheet will be saved as

If Worksheets("BidTab").Range("G12") = "" Then
    ans = MsgBox("This form is not ready to be saved", vbOKOnly, "Bid Tabs")
    Select Case ans
        Case vbOK
        Exit Sub
    End Select
End If

'Requests user to enter in short name for project
Msg = "Enter Project Short Name"
ProjectShortName = InputBox(Msg, "Save As")

' TRIAL is added here until project is compelted.
BTFName = "TRIAL " & Worksheets("Initial Entry").Range("B5") & " " & ProjectShortName & _
    " " & "Bid Tab Results" & " " & Worksheets("BidTab").Range("L5")
' Add in a cancle option to this msgbox
MsgBox BTFName
BTFDate = Year(Now())
BTFfolder = "M:\DotserverD\Daves Snapserver Files Construction Files\Bid Tabs\" & BTFDate _
    & "\County"
    Debug.Print BTFfolder
Set NewBook = Workbooks.Add
ThisWorkbook.Worksheets("BidTab").Copy Before:=NewBook.Sheets(1)

NewBook.SaveAs Filename:=BTFfolder & "\" & BTFName & ".xlsx", FileFormat:=xlOpenXMLWorkbook

End Sub

No, I was looking to leave the values in place and remove the formulas. — Decoy26, Jan 12 '17 at 20:07
then pasteSpecial xlPasteValues. look at the same question here : http://stackoverflow.com/questions/23937262/excel-vba-copy-paste-values-only-xlpastevalues — D. O., Jan 12 '17 at 20:10

A.S.H · Accepted Answer · 2017-01-12T19:59:50.110

2

ThisWorkbook.Worksheets("BidTab").Copy Before:=NewBook.Sheets(1)

Put this after the above statement:

With NewBook.Sheets(1).UsedRange
    .Value = .Value
End With

This will remove the links and keep only the values in the new worksheet.

edited Jan 12 '17 at 19:59

answered Jan 12 '17 at 19:51

A.S.H

29,101
5
23
50

Thanks! That worked well. Is there a way to set the two command buttons I have on the original workbook as not visible or remove them all together when this saves? – Decoy26 Jan 12 '17 at 20:09
@Decoy26 welcome, glad to know it worked. To remove a control from from the worksheet you can span the worksheet's `Shapes` Collection and check for its `.name`property and call `.delete` on it. – A.S.H Jan 12 '17 at 20:53

Hrothgar · Answer 2 · 2017-01-12T20:33:29.030

I have this in a similar book. You can probably simplify it.

Dim shShape As Shape
    For i = 1 To UBound(sheetNames)
        mSaveWorkbook.Sheets(i).Name = sheetNames(i)
        If mSaveWorkbook.Sheets(i).Shapes.Count > 0 Then
            For Each shShape In mSaveWorkbook.Sheets(i).Shapes
                If shShape.Type = msoFormControl Then
                    shShape.Delete
                End If
            Next shShape
        End If
    Next i
End If

Saving a worksheet into a new workboook but using values instead of the referencing formulas

2 Answers2