vba - 将工作表保存到新工作簿中，但使用值而不是引用公式

Question

因此，我尝试创建一个工作簿，该工作簿使用初始数据工作表中的多个引用来自动填充不同工作表中的单元格以生成表单(预先格式化的工作表)。对于其中一个工作表，我需要将其作为其自己的 .xlsx 工作簿保存在单独的网络驱动器上。到目前为止，我开发的代码创建了新的工作簿，但所有单元格仍然包含引用原始工作簿的原始公式。有没有办法在保存到新工作簿时将单元格转换为值？这是我现有的子系统。 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

Answer 1

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

将其放在上述语句之后:

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

这将删除链接并仅保留新工作表中的值。