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 used the code below to convert multiple worksheets within a woorkbook to to values. Its useful to shrink file size when emailing reports where the user does not need to make changes.
    I've created a new workbook, but this one contains only Excel pivot tables. The macro doesn't convert the pivot tables to values the way it did with formulas. I suppose that I could try to change the pivot table setting to not allow the data to be stored with the pivot table, but I'd rather not. Can you modify this macro to convert the pivot table to values the way it works with all the other formulas?


    Sub SetAllSheetsToValues()
    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

    End Sub

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    All output of a pivot table is Values. As long as a User does not attempt to "Refresh" the data you could send the the workbook and delete the data. To prove this just elimiate the data in the workbook and the Pivot Table should not change (unless you refresh).

    Regards,

    Tom D

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    - I don't want the user to drill-down to the detail
    - I want to reduce file size to attach the reports to an email (nine worksheets with twelve pivot tables for 1200 records)
    - I'd rather not change the two PivotTable Options - Data - PivotTables Data - Save source data or Enable show detail

    Perhaps the answer lies in this line of code:
    Set rngR = .UsedRange.SpecialCells(xlCellTypeFormulas, _
    xlErrors + xlLogical + xlNumbers + xlTextValues)

    Is there a way to identify pivot tables in the used range?
    I added "+ xlPivotTables" as an object in the line to test the modification.
    VB didn't give me a syntax error, but it also did not produce the desired/expected results. So maybe thats not it.

  4. #4
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    I tried the below on a Pivot Table and it would not allow any drill downs.
    The below will work on the active sheet. If you wnat ot do more sheets you will need to creat a VBA Loop


    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False

    End Sub

Posting Permissions

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