VBA - saving every sheet as values in excel workbook

Question

I'm trying to write a macro to loop through every worksheet in the active workbook, and save all formulas as values. To do this, for each sheet, first I run through each pivot table, then select the table and copy & paste as values. Then I am trying to use the worksheet.activerange.value = .value method to save the rest of the cells in the sheet.

I am getting a 1004 runtime error on the line wks.UsedRange.Value = wks.UsedRange.Value.

I have two questions:

1) How can I fix my runtime error?

2) Is there a way to get .value = .value to work with pivot tables? In previous macros this has never worked with pivots, so I have to use copy and paste as below.

Many thanks for your help!

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

Answer 1

On The King's point, you can simply copy and paste the whole sheet as values.

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

If you wanted to throw that in a simple loop to do it to all pages it can look like:

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

Please let me know if that helped!