vba - 将多个工作表中的公式转换为值

Question

下面的 VBA 代码旨在将多个工作表中的公式转换为值，然后将工作簿的副本保存在指定目录中。

我试图只复制粘贴值，但工作簿仍然保存这些工作表中的公式。我不知道我做错了什么，这段代码似乎不起作用

Sub CREATE4SHEETS()
Sheets(Array("sheet1", "sheet3", "sheet6", "sheet7", "sheet8", "sheet10")).Select
Sheets("sheet10").Activate
Sheets(Array("sheet1", "sheet3", "sheet6", "sheet7", "sheet8", "sheet10")).Copy

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
ChDir "\\mac\desktop\" ' Name folder    
ActiveWorkbook.SaveAs Filename:= _
    "\\Mac\Desktp\newworkbook.xlsx" _
    , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False ' need to change the name of the folder
ActiveWorkbook.Save
End Sub

看起来

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

没有按预期工作。有谁知道为什么？谢谢!

Answer 1

以下修改后的代码会将指定工作表中的任何公式转换为值，然后使用指定的文件名保存工作簿。

Sub CREATE4SHEETS()
Dim WS as Worksheet
Application.DisplayAlerts = False
'For each WS in Sheets(Array("sheet1", "sheet3", "sheet6", "sheet7", "sheet8", "sheet10"))
'    WS.UsedRange.Value2 = WS.UsedRange.Value2
'Next WS

For each WS in Worksheets
    If (UBound(Filter(Array("sheet1", "sheet3", "sheet6", "sheet7", "sheet8", "sheet10"), WS.Name)) > -1) Then
        'Keep this worksheet
        WS.UsedRange.Value2 = WS.UsedRange.Value2
    Else
        WS.Delete
    End If
Next WS

Set WS = Nothing

ActiveWorkbook.SaveAs Filename:= _
    "\\Mac\Desktp\newworkbook.xlsx" _
    , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False ' need to change the name of the folder
Application.DisplayAlerts = True
End Sub