title: Export Pivot Table and Source data to another Workbook
body: <p>I need to export a pivot table and its source data to another Excel Workbook. I have written this function to do it:</p>
  
  <pre><code>Public Function SaveASSheets (sheetsArray As Variant, destination As String)    
      Sheets(sheetsArray).Copy
      ActiveWorkbook.SaveAs destination, 50
      ActiveWorkbook.Close    
  End Function
  </code></pre>
  
  <p>sheetsArray is an array with PivotTable and PivotTable source data worksheets
  destination is a full path where I want the new Excel file (path + fine name + extension (.xlsb))</p>
  
  <p>The problem I have when I execute this code is new pivot table saved in a new file in destination folder is pointing to the old pivot table source data, instead of using the source data tab I have copied with it.
  Data source range in the Name Manager that I use for the old pivot table exist in both files (new and old) but the pivot table in the new file is pointing to the data source range in the old file.</p>
  
  <p>I have tried to reassign new pivot table data source but I got an error: </p>
  
  <blockquote>
    <p>"Excel cannot complete this task with available resources, Chose less data or close other applications"</p>
  </blockquote>
  
  <p>This is my the code:</p>
  
  <pre><code>Public Function SaveASSheets(sheetsArray As Variant, destination As String, Optional pivotTableRange As Range)    
      Sheets(sheetsArray).Copy
      ActiveWorkbook.SaveAs destination, 50
      For Each Sheet In ActiveWorkbook.Worksheets
          For Each Pivot In Sheet.PivotTables
              If Not pivotTableRange Is Nothing Then
                  Pivot.SourceData = pivotTableRange
              End If
              Pivot.RefreshTable
              Pivot.Update            
          Next
      Next
      ActiveWorkbook.Close    
  End Function
  </code></pre>