Pivot Table Refresh Issue. The PivotTable report was saved without the underlying data

[mrmmickle1]

I have a large macro that runs this Sub Procedure at the end of it:

[COLOR=#0000ff]Sub [/COLOR]RefreshData()

  [COLOR=#0000ff]  Dim [/COLOR]YTDSourceName      [COLOR=#0000ff]As Range[/COLOR]
  [COLOR=#0000ff]  Dim[/COLOR] CurrentSourceName [COLOR=#0000ff] As Range[/COLOR]
  [COLOR=#0000ff]  Dim[/COLOR] YTDLRow            [COLOR=#0000ff]As Long[/COLOR]
    [COLOR=#0000ff]Dim[/COLOR] CurrentLRow       [COLOR=#0000ff] As Long[/COLOR]
 [COLOR=#0000ff]   Dim[/COLOR] ManagerLRow        [COLOR=#0000ff]As Long[/COLOR]
    
    [COLOR=#0000ff]On Error GoTo[/COLOR] ErrorTrap
    
[COLOR=#008000]    'Define Ranges For Pivot Tables[/COLOR]
    CurrentLRow = Sheets("RawData").Range("A" & Rows.Count).End(xlUp).Row
    ManagerLRow = Sheets("Calculations").Range("B" & Rows.Count).End(xlUp).Row
    Set CurrentSourceName = Worksheets("RawData").Range("A1:G" & CurrentLRow)
    
   [COLOR=#008000] 'Update Pivot Tables[/COLOR]
    Sheets("Calculations").PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:=CurrentSourceName _
       , Version:=xlPivotTableVersion14)
       
    Sheets("Calculations").PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:=CurrentSourceName _
       , Version:=xlPivotTableVersion14)
       
    Sheets("Calculations").PivotTables("PivotTable3").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:=CurrentSourceName _
       , Version:=xlPivotTableVersion14)
       
    Sheets("DashBoard").PivotTables("PivotTable4").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:=CurrentSourceName _
       , Version:=xlPivotTableVersion14)
       
[COLOR=#008000]    'Update Charts[/COLOR]
    Sheets("Dashboard").ChartObjects("mgrChart").Chart.SetSourceData Source:=Sheets("Calculations").Range("B3:C" & ManagerLRow)
    Sheets("Dashboard").ChartObjects("errChart").Chart.SetSourceData Source:=Sheets("Calculations").Range("V32:W49")
       
[COLOR=#008000]    'Refresh All Pivots[/COLOR]
    ActiveWorkbook.RefreshAll
[COLOR=#008000]    
    'Collapse All Fields[/COLOR]
    Sheets("DashBoard").PivotTables("PivotTable4").PivotFields("Client Name").ShowDetail = False
    Sheets("DashBoard").PivotTables("PivotTable4").PivotFields("Manager").ShowDetail = False
    
[COLOR=#0000ff]    Exit Sub[/COLOR]
    
ErrorTrap:

MsgBox "Err Num:- " & Err.Number & Chr(13) & "Err Desc:- " & Err.Description & Chr(13) & Chr(13) _
& "There was an error.  Please contact file administrator for assistance.", vbCritical, strTitle

[COLOR=#0000ff]On Error GoTo 0[/COLOR][COLOR=#008000] 'Reset Error Handling[/COLOR]
  
[COLOR=#0000ff]End Sub[/COLOR]

My issue is that after I run my macro and the file is updated/refreshed, I will save the file as: Error Report (TODAY'S DATE).xlsx. When I send my file out to recipients via email the file seems not to be updated anymore. The user has to go to the Data Tab and hit Refresh All... it wouldn't be that big of a deal but, it takes 45 secs to a 2 minutes for the data to refresh.

Once the data is refreshed the file moves fairly fast. Is there a way to prevent the user from having to refresh the data upon opening the email?

The error I get is: The PivotTable report was saved without the underlying data. Use the Refresh Data command to update the report.

 

[Jerry Sullivan]

You can set your PivotTable's to have the underlying data saved in the worksheet.

PivotTable Options > Data tab > check "Save source data with file"

If that's checked, the PivotCache is stored within the workbook and will be loaded when the workbook is opened (by you or other users that receive your workbook).
You can even delete the data source range from the workbook to make its file size smaller.

If that option isn't checked, the PivotCache(s) will need to be refreshed sometime after reopening the workbook before the PivotTable(s) can be updated.

Alternatively, you can opt not to check "Save source data with file", and check the PivotTable option: "Refresh data when opening the file."

 

[mrmmickle1]

Jerry,

I appreciate you explaining these two options for me. I wan't aware these existed. That should fix my issue!