Quote Originally Posted by Ray707 View Post
when it gets sent, stakeholders can change the filter in the pivot tables and view other departments costs
With a pivot table, there is the option to save/not save the data with the file.
You could try one of the following:
1. With your working/original file make sure all pivot tables that you might send have this option set to not save the data with the file as follows: right-click somewhere in the pivot table and choose PivotTable options…, then in the Data tab look for the checkbox: Save source data with file (it's usually the first checkbox) and make sure it's unticked.
If the source data's in the original file, but not in the file you send this should be OK.
2. When you're creating the file to send, have the vba turn that option off for you in the file you send with the likes of 
    ActiveSheet.PivotTables("PivotTable1").SaveData = False
for each pivot table.

Otherwise, to lose the pivot altogether and save only the hard values (make sure the original data isn't still elsewhere in the sent spreadsheet!) you can do the likes of: 
With ActiveSheet.PivotTables("PivotTable1").TableRange2
  .Copy
  .PasteSpecial Paste:=xlPasteValuesAndNumberFormats
End With
But much of this and more was already discussed in a previous thread of yours about a month ago: http://www.vbaexpress.com/forum/show...Cat&highlight=