Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    I have a macro which selects all sheets in a workbook and then converts the formulas to values so that I can email the file in the smallest possible form.
    I would like to add some pivot tables to the workbook, but the macro converts only the formulas, not the pivot tables.
    How can this code be modified to include pivot tables?

    'Replace all formulas in all sheets with values

    Dim shtSheet As Worksheet, shtActive As Worksheet
    Dim rngR As Range, rngCell As Range
    Application.ScreenUpdating = False
    Set shtActive = ActiveWorkbook.ActiveSheet
    For Each shtSheet In ActiveWorkbook.Sheets
    With shtSheet
    If .ProtectContents = False Then ' skip protected sheets
    On Error Resume Next
    Set rngR = .UsedRange.SpecialCells(xlCellTypeFormulas, _
    xlErrors + xlLogical + xlNumbers + xlTextValues)
    On Error GoTo 0
    If Not rngR Is Nothing Then
    For Each rngCell In rngR
    rngCell.Value2 = rngCell.Value2
    Next rngCell
    End If
    End If
    End With
    Next shtSheet
    shtActive.Activate ' reset to original active sheet
    [a2].Activate
    Set shtActive = Nothing
    Application.ScreenUpdating = True

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could add this line at the beginning:

    Dim pvt As PivotTable

    And within the With shtSheet ... End With block:

    Code:
    For Each pvt in shtSheet.PivotTables
      pvt.TableRange2.Copy
      pvt.TableRange2.PasteSpecial Paste:=xlPasteValues
    Next pvt

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thank you Hans. Works like a charm!

    I'm posting the finished code in case someone else could use it.

    ' Replace all formulas and Pivot Tables with values


    Dim pvt As PivotTable

    Dim shtSheet As Worksheet, shtActive As Worksheet
    Dim rngR As Range, rngCell As Range
    Application.ScreenUpdating = False
    Set shtActive = ActiveWorkbook.ActiveSheet

    For Each shtSheet In ActiveWorkbook.Sheets
    With shtSheet
    If .ProtectContents = False Then ' skip protected sheets
    On Error Resume Next
    Set rngR = .UsedRange.SpecialCells(xlCellTypeFormulas, _
    xlErrors + xlLogical + xlNumbers + xlTextValues)
    On Error GoTo 0
    If Not rngR Is Nothing Then
    For Each rngCell In rngR
    rngCell.Value2 = rngCell.Value2
    Next rngCell
    End If
    End If
    For Each pvt In shtSheet.PivotTables
    pvt.TableRange2.Copy
    pvt.TableRange2.PasteSpecial Paste:=xlPasteValues
    Next pvt

    End With
    Next shtSheet
    shtActive.Activate ' reset to original active sheet
    [a2].Activate
    Set shtActive = Nothing
    Application.ScreenUpdating = True

  4. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    Chicago, IL
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Actually, you may want to consider an even simpler approach - just replace all contents with values, as follows:

    Code:
    Dim sht As Worksheet
    For Each sht In Application.Worksheets
        sht.Cells.Copy
        sht.Cells.PasteSpecial Paste:=xlPasteValues
    Next
    In this fashion, you are eliminating all non-value entries on all sheets...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •