VBA - 将每个工作表保存为Excel工作簿中的值

Question

我正在尝试编写一个宏，以循环遍历活动工作簿中的每个工作表，并将所有公式保存为值。为此，对于每个工作表，我首先遍历每个数据透视表，然后选择该表并将其复制并粘贴为值。然后我尝试使用worksheet.activerange.value = .value方法保存工作表中剩余的单元格。

在wks.UsedRange.Value = wks.UsedRange.Value这一行会出现运行时错误1004。

我的两个问题是：

1) 我该如何修复运行时错误？

2) 是否有办法让.value = .value与数据透视表一起使用？在之前的宏中，这从未与数据透视表一起使用，因此我必须像下面这样使用复制和粘贴。

非常感谢您的帮助！

Sub LockWorkbook()

Dim pvt As PivotTable
Dim wks As Worksheet
Dim i As Integer
Dim n As Integer

n = 1

For Each wks In ActiveWorkbook.Worksheets
    i = 1
    For Each pvt In wks.PivotTables
        wks.PivotTables(i).TableRange2.Select
        With Selection
            .Copy
            .PasteSpecial xlValues
            .PasteSpecial xlFormats
        End With
        i = i + 1
        Next pvt

    Set wks = ActiveWorkbook.Worksheets(n)
    wks.UsedRange.Value = wks.UsedRange.Value
    n = n + 1

    Next wks

End Sub

- sgtstaine

---

难道不应该使用 usedrange.formula = usedrange.value 吗？抱歉，也许我错了。 - The King

此外，数据透视表不能包含公式。它们只能包含值，因此您不需要像这样的宏。 - RBarryYoung

“cells.copy cells.pastespecial xlValues” 这个方法能否用于整张包括数据透视表的工作表？ - The King

Answer 1

在King的角度上，您可以将整个表格复制并粘贴为值。

Sheets("Sheet1").cells.copy
Sheets("Sheet1").cells.PasteSpecial Paste:=xlPasteValues

如果你想将它放入一个简单的循环中，并对所有页面执行操作，可以这样写：

Sub Test1()

    Dim WS_Count As Integer
    Dim I As Integer


    WS_Count = ActiveWorkbook.Worksheets.Count

    For I = 1 To WS_Count

        ActiveWorkbook.Worksheets(I).Cells.Copy
        ActiveWorkbook.Worksheets(I).Cells.PasteSpecial Paste:=xlPasteValues

    Next I

End Sub

请告诉我这是否有帮助！

- Rookz

Answer 2

最新修改消除了运行时错误 '1004'（不包括将数据透视表转换为值）

Option Explicit

Public Sub ConvertFormulasToValuesInUsedRangeExceptPivotTables()
    Dim ws As Worksheet, ur As Range, cel As Range
    Dim pvt As PivotTable, nonPivot As Range, isPvt As Boolean

    For Each ws In ThisWorkbook.Worksheets
        Set ur = ws.UsedRange

        For Each cel In ur

            For Each pvt In ws.PivotTables      'excludes pivot tables
                isPvt = Not Intersect(cel, pvt.TableRange2) Is Nothing
                If isPvt Then Exit For
            Next

            If Not isPvt Then
                If Len(cel.Formula) > 0 Then    'excludes empty cells
                    If nonPivot Is Nothing Then
                        Set nonPivot = cel
                    Else
                        Set nonPivot = Union(nonPivot, cel)
                    End If
                End If
            End If

        Next cel

        nonPivot.Value = nonPivot.Value
        Set nonPivot = Nothing

    Next ws

End Sub

- paul bica

---

我仍然在"运行时错误'1004': 应用程序或对象定义错误"这一行上遇到问题："ws.usedrange.value = ws.usedrange.value"。有什么想法吗？ - sgtstaine

好的，所以它可以在一个简单的工作簿中运行（我打开一个新文件并进行了一些sum()计算）。但是它无法在更复杂的文件中运行（包含数据透视表、条件格式、合并单元格和链接到其他文件）。你知道可能是什么原因吗？ - sgtstaine

你的最后一条评论很好 - 我只是用一个简单的文件（有3个数据透视表）进行了测试。我认为数据透视表不会引起问题，但我会添加更多的公式、验证规则、条件格式、合并单元格和链接到其他文件。你在文件中还有什么？另外 - 错误编号和描述是什么？ - paul bica

始终出现“运行时错误'1004'：应用程序定义或对象定义错误”。 - sgtstaine

最新的编辑（非常晚）消除了运行时错误“1004”（不包括将数据透视表转换为值）。 - paul bica