title: VBA - saving every sheet as values in excel workbook
body: <p>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 &amp; paste as values. Then I am trying to use the <code>worksheet.activerange.value = .value</code> method to save the rest of the cells in the sheet.</p>
  
  <p>I am getting  a 1004 runtime error on the line <code>wks.UsedRange.Value = wks.UsedRange.Value</code>.</p>
  
  <p>I have two questions:</p>
  
  <p>1) How can I fix my runtime error?</p>
  
  <p>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.</p>
  
  <p>Many thanks for your help!</p>
  
  <pre><code>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
  </code></pre>